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.
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
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.
ASKER
I can definitely confirm that the books table has a column named "id" and the books_list table has a column named "books_id".
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
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
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
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
ASKER
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??
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??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That'll work, thanks!