CODEWITHSUNDEEP
mysqlsqljoin
Elen
Elen

Reputation: 2343

Select from 2 tables, both using JOIN and connected by ID

It's hard to search anywhere on this subject... Basically, I'm selecting from two tables, each of the table has JOIN clauses. If I separate queries by each table, both work fine... Below looks logically correct to me.. But it gives me an error: 

Error Code: 1054. Unknown column 'catalogue.ID' in 'on clause'  0.000 sec

Is this query possible, and what am I missing here?

SELECT  `catalogue`.`ID`,`catalogue`.`Catalogue`,`catalogue`.`Artist`,`catalogue`.`Title`, `catalogue`.`Street_Date`,
        `label`.`label_name` as `label`,
        `format`.`format_name` as `Format`,
        `schedule`.designer,`schedule`.`MO`,

        `packtype`.`description` as `Package`,
        `manufacturer`.`description` as `Manufacturer`

        FROM `catalogue`, `manufacture`

        LEFT JOIN `schedule` ON (`schedule`.`cat_id` = `catalogue`.`ID`) 
        LEFT JOIN `label` ON (`catalogue`.`label` = `label`.`Lbl_ID`) 
        LEFT JOIN `format` ON (`catalogue`.`format` = `format`.`Fmt_ID`) 

        LEFT JOIN `packtype` ON `packtype`.`pack_id` = `manufacture`.`pack_id`
        LEFT JOIN `manufacturer` ON `manufacturer`.`man_id` = `manufacture`.`manuf`

    WHERE `catalogue`.`Street_Date`="2014-03-17" and  `manufacture`.`cat_id` = `catalogue`.`ID`

    ORDER BY  `catalogue`.`Catalogue`  asc LIMIT 1 , 10

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You are mixing join types, so this is one of the problem you get. You can fix this by replacing the , with cross join:

    FROM `catalogue` cross join `manufacture`

    LEFT JOIN `schedule` ON (`schedule`.`cat_id` = `catalogue`.`ID`) 
    LEFT JOIN `label` ON (`catalogue`.`label` = `label`.`Lbl_ID`) 
    LEFT JOIN `format` ON (`catalogue`.`format` = `format`.`Fmt_ID`) 

    LEFT JOIN `packtype` ON `packtype`.`pack_id` = `manufacture`.`pack_id`
    LEFT JOIN `manufacturer` ON `manufacturer`.`man_id` = `manufacture`.`manuf`

However, it surprises me that there are no join conditions between these tables.

Oh, I see the condition in the where. Yuck!

    FROM `catalogue`
    LEFT JOIN `manufacture` ON `manufacture`.`cat_id` = `catalogue`.`ID`

    LEFT JOIN `schedule` ON (`schedule`.`cat_id` = `catalogue`.`ID`) 
    LEFT JOIN `label` ON (`catalogue`.`label` = `label`.`Lbl_ID`) 
    LEFT JOIN `format` ON (`catalogue`.`format` = `format`.`Fmt_ID`) 

    LEFT JOIN `packtype` ON `packtype`.`pack_id` = `manufacture`.`pack_id`
    LEFT JOIN `manufacturer` ON `manufacturer`.`man_id` = `manufacture`.`manuf`

This problem is actually documented:

However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.

The simple solution is: NEVER use , in the from clause. If you want a cross join, then use cross join.

Upvotes: 5

Related Questions