开发者

problem with sql left join

开发者 https://www.devze.com 2023-02-28 13:26 出处:网络
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 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.

0

精彩评论

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