MySQL join with 2 conditions error

Question

I can't figure out why I get an SQL error with the mySQL statement (in php file) below. I think the problem relates to the second condition 'AGREEDPRODUCTS.productid_corporation is null'. I checked that the syntax of the used parameters is correct as used in the database. I also tried other alternatives with respect to the second condition (like using WHERE but that is obvious not allowed;MySQL join with where clause) but those didn't work.

$sqlquery4 = "SELECT AGREEDPRODUCTS.id,AGREEDPRODUCTS.productid_supplier,EMETERPRODUCTS.productname "
                . "FROM AGREEDPRODUCTS "
                . "INNER JOIN EMETERPRODUCTS "    
                . "ON AGREEDPRODUCTS.productid_supplier=EMETERPRODUCTS.productid AGREEDPRODUCTS.producttype='EMETER' AND AGREEDPRODUCTS.productid_corporation is null "
                . "ORDER BY AGREEDPRODUCTS.productid_supplier";

Any suggestions?

score 3 · Accepted Answer · answered Nov 08 '13 at 11:22

3

You've missed an AND, or other separator, on that line:

ON AGREEDPRODUCTS.productid_supplier=EMETERPRODUCTS.productid AGREEDPRODUCTS.producttype='EMETER' AND AGREEDPRODUCTS.productid_corporation is null
                                                            ^^^

answered Nov 08 '13 at 11:22

Marcus

11,928
5
45
65

thnx (both). Feel very thick that I have overlooked that ;-( – Joppo Nov 08 '13 at 12:07

score 0 · Answer 2 · answered Nov 08 '13 at 11:24

ON AGREEDPRODUCTS.productid_supplier=EMETERPRODUCTS.productid AND AGREEDPRODUCTS.producttype='EMETER' AND AGREEDPRODUCTS.productid_corporation is null

You're missing an AND........................................................................................^here^.

And you confused something here:

like using WHERE but that is obvious not allowed

The WHERE in this question makes the left/right join to an inner join. That's why you have to put it in the join condition. A WHERE clause is always possible. You could as well also put your second join condition in a where clause.

MySQL join with 2 conditions error

2 Answers2