SQL join - unknown column?

Why would this fail?

The error message is "Unknown column 'b.id' in 'on clause'."

My goal is to bring in all the rows of the books and books_users tables matching with the WHERE clause, and then bringing in any other matching rows (but not required to be there) from the tables books_lists and lists.

SELECT DISTINCT b.*, bl.*
  FROM books b, books_users bu
  JOIN books_lists bl ON bl.books_id = b.id
  JOIN lists l ON l.id = bl.lists_id AND l.isLive = 1
 WHERE bu.users_id = '1'
   AND bu.books_id = b.id
   AND bu.relationship = '1'
GROUP BY b.title
ORDER BY bu.create_date

Open in new window

pnoericAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wilhelmshCommented:
Can you query the books table by itself and ensure that the column you are linking on in your join is actually named id, if it is not, then use the column name from the books table that links to the books_lists table.
0
pnoericAuthor Commented:
I can definitely confirm that the books table has a column named "id" and the books_list table has a column named "books_id".
0
wilhelmshCommented:
I would try the following query and see if it returns part of what you are looking for and then add in the additional information required:

SELECT DISTINCT b.[id], b.title, bl.books_id
  FROM books b
  JOIN books_lists bl ON bl.books_id = b.id
  GROUP BY b.title
ORDER BY b.title
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

wilhelmshCommented:
Sorry I meant to add [] around the second id also:

SELECT DISTINCT b.[id], b.title, bl.books_id
  FROM books b
  JOIN books_lists bl ON bl.books_id = b.[id]
  GROUP BY b.title
ORDER BY b.title
0
pnoericAuthor Commented:
Very strange. THIS works:

SELECT DISTINCT b.*, bl.*
  FROM books b, books_users bu, books_lists bl

 WHERE bu.users_id = '2'
   AND bu.books_id = b.id
   AND bu.relationship = '1'

  GROUP BY b.title
ORDER BY b.title


...but as soon as add one line, the JOIN, it fails with the same error message (unknown column b.id):

SELECT DISTINCT b.*, bl.*
  FROM books b, books_users bu, books_lists bl
  JOIN books_lists ON b.id = bl.books_id

 WHERE bu.users_id = '2'
   AND bu.books_id = b.id
   AND bu.relationship = '1'

  GROUP BY b.title
ORDER BY b.title



What's going on here??
0
wilhelmshCommented:
Try this query:

SELECT DISTINCT b.*, bl.*
FROM books b, books_users bu, books_lists bl, lists l
WHERE bl.books_id = b.id
   AND l.id = bl.lists_id AND l.islive = 1
   AND bu.users_id = '1'
   AND bu.books_id = b.id
   AND bu.relationship = '1'
GROUP BY b.title
ORDER BY bu.create_date

You may need to put the [] around id as I believe that is a reserved word in SQL.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pnoericAuthor Commented:
That'll work, thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.