ANDing in join condition???

Question

Is it possible to use AND in a join condition?

For example:

   SELECT * 
     FROM items  
LEFT JOIN products as prod ON prod.prod_id = items.prod_id  
LEFT JOIN partial as p ON p.prod_model = prod.prod_model 
                      AND p.order_id=$order_id  
    WHERE order_id = $order_id  
 ORDER BY prod.prod_weight ASC

I am guessing it will not work. $order_id is passed in via a PHP variable. What I want to happen is if there is a row in the partial table with the same order_id and prod_model then add it to the row..and if not just keep the item.* and prod.*

I know there HAS to be a better way to do this but I am just drawing blanks right now.

Answer 1

Did you try it and see a problem. Yes you can do AND in joins.

Answer 2

Your query should work.

For inner joins it's functionally equivalent to AND the secondary join criteria to the ON clause or to put it in the WHERE clause, but in your case you are doing an outer join so you want it ANDed to the ON clause, just like you wrote it.

Answer 3

I'm not sure if the parentheses are mandatory, but it's certainly clearer if you add some:

LEFT JOIN partial as p ON (p.prod_model = prod.prod_model 
                           AND p.order_id = $order_id)