CODEWITHSUNDEEP
mysqlsql
Nevada
Nevada

Reputation: 277

MySQL JOIN select all

I have following tables:

BOOK

book_id | book_name | book_photo

RUBRIC

rubric_id | rubric_name

AUTHOR

author_id | first_name | last_name

BOOK_RUBRIK

book_id | rubric_id

BOOK_AUTHOR

book_id | author_id

I want to select using JOIN like this:

SELECT 
  book.book_name, 
  autor.first_name, 
  autor.last_name, 
  rubric_rubric_name 
FROM book 
  INNER JOIN book_autor ON book.book_id = autor_id 
  LEFT JOIN book_rubric ON book.book_rubric = rubric_id 
ORDER BY book.book_id;

But it not work. How to select book_name, rubric_name, first_name and_last_name?

Upvotes: 1

Views: 102

Answers (2)

Sean Zhang
Sean Zhang

Reputation: 108

select book.book_name, author.first_name, author.last_name, rubric.ruburic_name
from book
    inner join book_author on book.book_id = book_author.book_id
    inner join author on book_author.author_id = author.author_id
    inner join book_rubric on book.book_id = book_rubric.rubric_id
    inner join rubric on book_rubric.rubric_id = rubric.rubric_id
order by book.book_id

Upvotes: 0

Bogdan Bogdanov
Bogdan Bogdanov

Reputation: 1723

You missed one table authors, so you can not extract data about the author:

SELECT 
  book.book_name, 
  autor.first_name, 
  autor.last_name, 
  rubric_rubric_name 
FROM book 
  INNER JOIN book_autor ON book.book_id = book_autor.book_id 
  INNER JOIN author ON book_autor.autor_id = autor.autor_id 
  LEFT JOIN book_rubric ON book.book_rubric = rubric_id 
ORDER BY book.book_id;

Adding INNER JOIN book_autor ON book.book_id = book_autor.book_id and INNER JOIN author ON book_autor.autor_id = autor.autor_id instead of INNER JOIN book_autor ON book.book_id = autor_id will fix the issue.

EDIT:

I missed fact that there are a few errors in the names in the authors script. I fix the select. Here is the propper one:

YOUR TABLE STRUCTURE:

BOOK
  book_id 
  book_name 
  book_photo

RUBRIC
  rubric_id 
  rubric_name

AUTHOR
  author_id 
  first_name 
  last_name

BOOK_RUBRIK
  book_id 
  rubric_id

BOOK_AUTHOR
  book_id 
  author_id

YOUR SELECT STATEMENT:

SELECT 
  b.book_name, 
  a.first_name, 
  a.last_name, 
  br.rubric_name 
FROM 
  BOOK AS b 
  INNER JOIN BOOK_AUTHOR ba ON b.book_id = ba.book_id 
  INNER JOIN AUTHOR a ON ba.author_id = a.author_id 
  LEFT JOIN BOOK_RUBRIK br ON b.book_id = br.book_id 
ORDER BY 
  b.book_id;

Upvotes: 1

Related Questions