mysql inner join

[SchweppesAle]

hi, we only have about 120 entries within the order table yet this join query is pulling in over 500.  Where did I goof? 

 

SELECT orders.orderid 
	, orders.cartid 
	, skus.skuid 
	, skus.price 
	, products.productid 
	, products.name  
FROM orders 
	INNER JOIN items ON 
		(items.cartid = orders.cartid) 
	INNER JOIN products ON 
		(products.productid = items.productid) 
	INNER JOIN skus ON 
		(skus.productid = products.productid)

[Mchl]

INNER JOIN in MySQL is equivalent to CROSS JOIN, which means that if you have 2 rows in table A

 

A:

ID

--

1,

2,

 

and three rows in table B referencing table A

 

B:

ID, AID

--

1,1

2,1

3,2

 

You will get three rows all together from A INNER JOIN B ON A.ID = B.AID

 

[SchweppesAle]

Oh man, I think I've somehow confused the joins then.

 

Say I wanted to combine all the tables together, but only for each entry of the order table.  Which join should I use?

[Mchl]

You should probably add GROUP BY clase and also some aggregating functions.

[SchweppesAle]

In short, the goal is to eliminate any duplicate order.orderid columns.  I thought that by setting orders as the primary table it would only match orders against each table then return an entry if a relationship where present among all four tables. 

[SchweppesAle]

You should probably add GROUP BY clase and also some aggregating functions.

 

GROUP BY clase did the trick, you're a genius.  Thanks

[Mchl]

Be aware though, that query using GROUP BY without aggregating functions (such as MAX(), SUM() etc) is not a correct query according to ANSI standard and also is unpredictable in what results will appear in 'not GROUPed BY' columns.

[SchweppesAle]

so instead of

 

SELECT orders.orderid 
	, orders.cartid 
	, skus.skuid 
	, skus.price 
	, products.productid 
	, COUNT(products.productid) AS productOrders 
	, products.name  
FROM orders 
	INNER JOIN items ON 
		(items.cartid = orders.cartid) 
	INNER JOIN products ON 
		(products.productid = items.productid) 
	INNER JOIN skus ON 
		(skus.productid = products.productid)
			GROUP BY orders.orderid 
				ORDER BY productOrders DESC 
					LIMIT 10

 

do this?

SELECT orders.orderid 
	, orders.cartid 
                , MAX(orders.cartid) AS maxOrders
	, skus.skuid 
	, skus.price 
	, products.productid 
	, COUNT(products.productid) AS productOrders 
	, products.name  
FROM orders 
	INNER JOIN items ON 
		(items.cartid = orders.cartid) 
	INNER JOIN products ON 
		(products.productid = items.productid) 
	INNER JOIN skus ON 
		(skus.productid = products.productid)
			GROUP BY orders.orderid 
				ORDER BY productOrders DESC 
					LIMIT 10

 

?

[Mchl]

It applies to each column not grouped. For example: if you have more than one product in a cart, you cannot be sure which products's name will be displayed by this query.