[SOLVED] query using left join

[jim.davidson]

I'm running mySQL ver 4.1.21 with PHP and Dreamweaver

 

I have three tables

orders - customers – manufacturers

 

Two variables

$confirmation_id - $customer_id

 

Need record set that contains

orders.order_date

orders.received_date

orders.updated_date

orders.received_by

orders.destroyed_by

orders.model_number

orders.serial_number

orders.capacity

orders.confirmation_id

customers.contact_name

customers.contact_phone

manufacturers.manufacture_name

 

 

I get this error when I run my query:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN manufacturers on orders.manufacturer_id=manufacturers.manufacturer_id WHERE' at line 3

 

Here my query:

 

SELECT orders.order_date, orders.received_date, orders.updated_date,orders.received_by,orders.destroyed_by,orders.model_number, orders.serial_number, orders.capacity, orders.confirmation_id, customers.contact_name, customers.contact_phone, manufacturers.manufacture_name FROM orders

LEFT JOIN customers on orders.customer_id = $customer_id

LEFT JOIN manufacturers on orders.manufacturer_id=manufacturers.manufacturer_id WHERE orders.confirmation_id =$confirmation_id",

 

Any idea what I'm doing wrong?

 

[jworisek]

LEFT JOIN customers on orders.customer_id = $customer_id

 

you arent stating how customers should be joined...  should probably be:

 

LEFT JOIN customers on (orders.customer_id = customers.customer_id and customers.customer_id = $customer_id)

[jworisek]

actually, I would move the $customer_id clause to the where...like

 

 

SELECT orders.order_date, orders.received_date, orders.updated_date,orders.received_by,orders.destroyed_by,orders.model_number, orders.serial_number, orders.capacity, orders.confirmation_id, customers.contact_name, customers.contact_phone, manufacturers.manufacture_name FROM orders
LEFT JOIN customers on orders.customer_id = customers.customer_id
LEFT JOIN manufacturers on orders.manufacturer_id=manufacturers.manufacturer_id 
WHERE orders.confirmation_id ='$confirmation_id' AND orders.customer_id = '$customer_id'

[jim.davidson]

Thanks for the help