Performance, multiple table Join?

scraff

Hello and thanks in advance for your help,

I have 3 tables i want to join up, I know how to inner join 2 tables, but then doing a seperate query for the 2rd table is wasting loaddsss of processing time (about 2 minutes).

This is for displaying Orders made by customers in a data base, the three tables are:

CDBorders (containing the order number & order date)
CDBorderformproducts (containing the list of products on the order: order number & product code
CDBProducts (containing the product code, and stock level)

Heres what I have (I have simplified this a bit):

$query = @mysql_query("
SELECT CDBorders.ordernumber, min(CDBorderformproducts.productcode), 
FROM CDBorders INNER JOIN CDBorderformproducts
ON CDBorders.ordernumber = CDBorderformproducts.ordernumber
WHERE CDBorderformproducts.sent=0 group by CDBorders.ordernumber

ORDER BY CDBorders.ordernumber
");

This is selecting from the CDBorders where products off CDBorderformproducts with the same order number have not been sent.

I want this to also check that the CDBproducts product (the details of the product) is showing it as instock=1. Is it possible to do similar to this:

$query = @mysql_query("
SELECT CDBorders.ordernumber, min(CDBorderformproducts.productcode),  CDBproducts.stock,
FROM CDBorders INNER JOIN CDBorderformproducts INNER JOIN CDBproducts
ON CDBorders.ordernumber = CDBorderformproducts.ordernumber
AND ON CDBorderformproducts.productcode = CDBproducts.productcode 
WHERE CDBorderformproducts.sent=0 AND CDBproducts.stock = 1 group by CDBorders.ordernumber

ORDER BY CDBorders.ordernumber
");

THANK YOU!

scraff

And btw, It just needs to select the orders where there are some products still left to be sent that are instock.

Weedpacket

Yes, it's possible to write joins on arbitrary numbers of tables. The MySQL manual should have details on the exact syntax.

johanafm

You'd need either

FROM a
	INNER JOIN (b
			INNER JOIN c ON b.field = c.field)
		) ON a.somefield = b_or_c.somefield

FROM a
	INNER JOIN b ON a.field=b.field
	INNER JOIN c ON a_or_b.somefield = c.somefield