开发者

Gathering data from 3 sql tables to create a single result

Wondering if anyone could help with the following problem.

I have three tables


table 1, Shop:

sID | sName

table 2, Customer:

cID | cFName | cLName

table 3, Transactions:

tID | cID | tInvoiceNo | sID | tPaymentType | tTotal

an example transaction in table 3 would be

1 | 001 | 1023 | S01 | CASH       | 20

2 | 001 |  -   | 002 | FRIENDGIFT | 10

First row would be an actual transction, between customer 001 (i.e bob) and shop S01 (i.e. toysRus) and that bob paid by cash ($20).

Row two would be that 001 (bob) gave customer 002 (jim) a gift of $10.

Problem

Thats all fine, but what im trying to get is an SQL statement that no only prints out the IDs like 001, 002 and S01 but the corresponding names... as an ID means nothing to the user.

for example an sql query on the past 5 transctions should gi开发者_JAVA百科ve me an output like

1 | 001 | bob | smith | 1023 | S01 | Toys R us | CASH | 20

2 | 001 | bob | smith |  -   | 002 | jane | black | FRIENDGIFT | 10

Condition

Everything apart from a payment type of FRIENDGIFT will always be an ID from table 1 (SHOP)... can you have IF statements in SQL? Maybe some sort of join?

Or is it impossible to do in a single query and needs to be split into 2? ... one to get past transactions and one to get the names for each ID depending on tPaymentType (i'm using php to get results through mysql so two queries wouldnt be a problem, just I'd like to handle as little queries as possible)

I hope I havent confused anyone :D

Thanks in advance!!


Solution

Big thank you to mario, here is the resulting sql query.

SELECT *
  FROM Transactions
  LEFT JOIN Customer USING (cID)
  LEFT JOIN Shop USING (sID)
  LEFT JOIN Customer c ON c.cID = sID
ORDER BY tID DESC
LIMIT 5


There is indeed a simple way to have a query accumulate data from multiple tables.

SELECT *
  FROM Transactions
  LEFT JOIN Customer USING (cID)
  LEFT JOIN Shop USING (sID)
ORDER BY tID DESC
LIMIT 5

You can use the simpler USING condition if the column names are identical in the tables you want to join. But there is also the ON clause which allows for other conditions (which however needs table aliases then).
See http://dev.mysql.com/doc/refman/5.1/en/join.html

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜