Unknown column on 'on clause' in sql joins?

Question

Can you guys help me please in understanding the SQL specifications on join. I don't understand it. I kept getting errors called Unknown column list on on clause. I got this error over my SQL syntax, I almost rubbed it in my face I just can't understand why it is not working, I have read some article regarding that it is because of precedence etc but I am really confused on what I have done wrong here.

select product.name , product.price from product inner join product_category on          
(product_category.product_no = product.product_no ) where product_category.sub_category = 
"COFFIN";

I know this question have been ask a hudred and million times here, but the ones I saw are complicated nowhere close in this very basic sql syntax.

THanks for helping me out.

EDIT: I just had realize that I have product_category not a direct child to my product table so I just have typed

 select * from product 
 join specifications 
 join product_category on ( specifications.product_no = product_category.product_no);

But this still gave me an error, unknown column product_category.

I've read and followed some instruction similarly to this sites: MYSQL unknown clause join column in next join Unknown column {0} in on clause MySQL "Unknown Column in On Clause"

I am really frustrated. I really can't get it to work.

Answer 1

For each new table you join in your query, each table must have at least one ON clause. It's hard to know exactly what you're trying to do without knowing the schema (table names, columns, etc), but here's an example

select *
from product p
join specifications s
    on p.product_no = s.product_no
join product_category pc
    on pc.spec_no = p.spec_no

Check out this link on table aliases as well. Gives a good example on joins + really useful info on how to increase the readability of your SQL http://msdn.microsoft.com/en-us/library/ms187455(v=sql.90).aspx

I found this article useful as well as it visually displays the different types of joins http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Answer 2

You are missing the part where you specify the joining conditions between product and specifcations.

select * from product 
join specifications YOU NEED SOMETHING HERE
join product_category on etc

Answer 3

I modified the SQL syntax to look like this, I have overlook a key that connects product_category onto specification so I made necessary link and it worked!!!

SELECT *
FROM product
JOIN specifications ON ( product.product_no = specifications.product_no )
JOIN product_category ON ( specifications.spec_no = product_category.spec_no )
WHERE product_category.sub_category = "COFFIN"
LIMIT 0 , 30

Also thanks for the heads up on missing joining condition on specifications. Heck this carelessness cost so much time. Thank you so much!

Answer 4

The default join type is an inner join. So if you write join, the database reads inner join, and insist that you include an on clause.

If you'd like to join without a condition, specify the cross join explicitly:

 select  * 
 from    product p
 cross join 
         specifications s
 inner join 
         product_category pc 
 on      pc.product_no = p.product_no
 left join
         some_other_table sot
 on      1=1

The last join, with the on 1=1 condition, is another way to do a cross join. It's subtly different in that it will return rows from the left table even if the right table is empty.

Example at SQL Fiddle.