With MySQL, how can i query a column from the table at 3 level depth?
I mean:
[main table] ---> [child table 1] ---> [child table 2]
> C#1's ID -------- > C#2's ID ------------ > String Column
For example:
[transaction]
- id
- bookid *
- date
- staff
[book]
- id
- authorid *
- title
[author]
- id
- name
By knowing only transaction.id, so how can i query for a result including following columns?
.. transaction.date , book.title , author.name ..
Add a comment
|
3 Answers
select t.date,b.title,a.name from book b
innerjoin transaction t on t.bookid = b.id
innerjoin author a on b.authorid = a.id
Comments
select transaction.date , book.title , author.name
from transaction
join book on transaction.bookid=book.id
join author on book.authorid=author.id
where transaction.id=<id>;
See more on joins here http://dev.mysql.com/doc/refman/5.5/en/join.html
answered Nov 24, 2011 at 10:38
David Rabinowitz
30.5k16 gold badges95 silver badges125 bronze badges
Comments
Use MySQL join syntax.
SELECT transaction.date , book.title , author.name
FROM transaction
LEFT OUTER JOIN book ON transaction.bookid=book.id
LEFT OUTER JOIN author ON book.authorid=author.id
WHERE transaction.id={your_transaction_id}
I use OUTER JOIN because that returns transaction even if book or author was deleted from DB. In this case result will be look like this: '2011-01-12',NULL,NULL
