Complex query collecting too many values

[luminous]

I'm trying to grab a set of infomation which is unique to an Order ID. Currently, for this query it should return 4 results (4 products) but it's bringing back 66  I think this is because it'd returning duplicate values. Here's the Query I'm working with at the moment:

 

SELECT  
O.`id_order`,

	O.`date_add`,
	O.`total_paid`,
	O.`total_shipping`,
	O.`delivery_date`,
	O.`invoice_date`,
	C.`id_gender`,
	AD.`firstname`,
	AD.`lastname`,
	AD.`company`,
	C.`email`,
	AD.`phone_mobile`,
	AD.`phone`,
	CONCAT(AD.`address1`, " ", AD.`address2`, " ", AD.`postcode`, " ", AD.`city`),
	CONCAT(AI.`address1`, " ", AI.`address2`, " ", AI.`postcode`, " ", AI.`city`),
	OD.`product_name`,
	OD.`product_quantity`,
	OD.`product_weight`,
	OD.`product_price`,
	M.`name`
                FROM ps_orders AS O,
                ps_customer AS C, 
                ps_order_detail AS OD, 
                ps_manufacturer AS M,
                ps_address AS AD, 
                ps_address AS AI 
               WHERE 
               O.id_order = 28,
               AI.id_address=id_address_invoice AND
               AD.id_address=id_address_delivery AND 
               C.id_customer=O.id_customer 
              

 

The 28 in this example will be inserted dynamically but for testing I'm using the number 28. If anyone can help me or needs any more info to help me, then please let me know!

 

Thanks!

[andrewgauger]

  O.id_order = 28,

              AI.id_address=id_address_invoice AND

 

should be:

  O.id_order = 28 AND
               AI.id_address=id_address_invoice AND

[luminous]

I did give that a try but the same thing is still happening

[andrewgauger]

SELECT  
O.`id_order`,

	O.`date_add`,
	O.`total_paid`,
	O.`total_shipping`,
	O.`delivery_date`,
	O.`invoice_date`,
	C.`id_gender`,
	AD.`firstname`,
	AD.`lastname`,
	AD.`company`,
	C.`email`,
	AD.`phone_mobile`,
	AD.`phone`,
	CONCAT(AD.`address1`, " ", AD.`address2`, " ", AD.`postcode`, " ", AD.`city`),
	CONCAT(AI.`address1`, " ", AI.`address2`, " ", AI.`postcode`, " ", AI.`city`),
	OD.`product_name`,
	OD.`product_quantity`,
	OD.`product_weight`,
	OD.`product_price`,
	M.`name`
                FROM ps_orders AS O,
                ps_customer AS C ON (O.id_customer = C.id_customer), 
                ps_order_detail AS OD, 
                ps_manufacturer AS M,
                ps_address AS AD ON (AD.id_address = O.id_address_delivery), 
                ps_address AS AI ON (AI.id_address = O.id_address_invoice) 
               WHERE 
               O.id_order = 28,

[luminous]

When i try your example i get:

 

#1064 - 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 'ON (O.id_customer = C.id_customer), ps_order_detail AS OD, ' at line 24

[andrewgauger]

take the very last comma out of the query.

SELECT  
O.`id_order`,

	O.`date_add`,
	O.`total_paid`,
	O.`total_shipping`,
	O.`delivery_date`,
	O.`invoice_date`,
	C.`id_gender`,
	AD.`firstname`,
	AD.`lastname`,
	AD.`company`,
	C.`email`,
	AD.`phone_mobile`,
	AD.`phone`,
	CONCAT(AD.`address1`, " ", AD.`address2`, " ", AD.`postcode`, " ", AD.`city`),
	CONCAT(AI.`address1`, " ", AI.`address2`, " ", AI.`postcode`, " ", AI.`city`),
	OD.`product_name`,
	OD.`product_quantity`,
	OD.`product_weight`,
	OD.`product_price`,
	M.`name`
                FROM ps_orders AS O,
                ps_customer AS C ON (O.id_customer = C.id_customer), 
                ps_order_detail AS OD, 
                ps_manufacturer AS M,
                ps_address AS AD ON (AD.id_address = O.id_address_delivery), 
                ps_address AS AI ON (AI.id_address = O.id_address_invoice) 
               WHERE 
               O.id_order = 28

 

It ended with a comma.

[luminous]

hey andrew,

 

Sorry for this and I do appreicate you time but after trying the query again with the removed comma I've noticed it's saying the error is at the:

 

'ON (O.id_customer = C.id_customer), ps_order_detail AS OD, 

 

I know this kind of query qould require and JOINS of some kind but I am inexperienced with MYSQL, do you have anywhere I could learn about these quickly or have anymore ideas how to get this working?

 

Thanks!

[Muddy_Funster]

try this:

                FROM ps_orders AS O INNER JOIN ps_customer AS C ON (O.id_customer = C.id_customer) INNER JOIN ps_address AS AD ON (O.id_address_delivery = AD.id_address AND O.id_address_invoice = AD.id_address_invoice), ps_order_detail AS OD, ps_manufacturer AS M

[andrewgauger]

FROM tbl_name , tbl_name

is equivalent to

FROM tbl_name INNER JOIN tbl_name

 

I'll look into this in approximately 5 hours when I have time.

[andrewgauger]

originally I was going to tell you that 1064 was a reserved words issue, and you should surround everything in back-ticks.