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
精彩评论