Question

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 ..

Answer 1

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

Answer 2

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

Answer 3

http://dev.mysql.com/doc/refman/5.0/en/join.html

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}

我使用<代码>OUTER JOIN,因为即使从银行删除图书或作者,也进行收益交易。在这种情况下,结果将照此办理:2011-01-12 ,NULL,NUL。

友情链接