i build 3 table
tb1 for book
tb2 for sub subject
tb3 for main subject
each book have 1 sub subject and each sub subject have 1 main subject i want make join between book=> sub subject => main subject
i make join between book and sub subject but cant make between sub subject with main subject
select book.id, book.titl, subsubject.title, mainsubject.title
from 开发者_运维百科book
left join subsubject on book.id = subsubject.book_id
i want complete this statement to add main subject
Wow, I think I understand... You want something like:
SELECT book.id, book.titl, subsubject.title, mainsubject.title FROM book,subsubject,mainsubject
WHERE book.id = subsubject.book_id AND
subsubject.book_id=mainsubject.book_id
You could replce the WHERE
with;
LEFT JOIN subsubject on book.id = subsubject.book_id
LEFT JOIN mainsubject on book.id = mainsubject.book_id
Also,do you mean book.title?
(My SQL may not be perfect, but should work)
Try this:
select book.id, book.titl, subsubject.title, mainsubject.title
from book
left join subsubject on book.id = subsubject.book_id
left join mainsubject on subsubject.book_id=mainsubject.book_id
The trick is probably that you need a different join condition between sub and main than between book and sub:
select *
from book join sub on book.id = sub.book_id
join main on sub.sub_id = main.sub_id
I'm not sure why a left (=outer) join would be required if each book must have one subject and each subject needs a main.
Try:
select b.id, b.title, s.title, m.title
from book b
left join subsubject s
on s.id = b.ss_id
left join mainsubject m
on m.id = b.m_id
where b.id = (id of book you'd like)
Hope this helps.
精彩评论