code_junkie

What's the best way to select multiple M:N relationships in SQL

Nov 13th, 2011
157
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.74 KB | None | 0 0
  1. books (book_id INT PRIMARY KEY, book_title VARCHAR)
  2. authors (author_id INT PRIMARY KEY, author_name VARCHAR)
  3. subjects (subject_id INT PRIMARY KEY, subject_name VARCHAR)
  4. book_authors (book_id INT, author_id INT)
  5. book_subjects (book_id INT, subject_id INT)
  6.  
  7. SELECT books.book_id, books.book_title,
  8. 'author' as record_type, authors.author_name as record_value
  9. LEFT JOIN book_authors ON books.book_id = book_authors.book_id
  10. LEFT JOIN authors ON authors.item_id = book_authors.author_id
  11. UNION
  12. SELECT books.book_id, books.book_title,
  13. 'subject' as record_type, subjects.subject_name as record_value
  14. LEFT JOIN book_subjects ON books.book_id = book_subjects.book_id
  15. LEFT JOIN subjects ON subjects.subject_id = book_subjects.subject_id;
Add Comment
Please, Sign In to add comment