[RESOLVED] Hard sql. Please help :-(

[errorCode30]

Hello all.

I have really hard sql problem. 🙁

If anyone could help, it would be great.

I have the following tables:

table1:

ItemId----soldQty--Sk
1-------------5-------XXL
2-------------6---------L
3-------------4--------XXL
3-------------5--------XXL
1-------------1--------XXL

table2:

ItemId - itemName - stock
1----------T-shirt X--------3
2---------T-Shirt Gre------4
3---------T-Shirt Red------6

What i would like to do is display the information like this:

ItemId--itemName---------stock - soldQty - Sk
1---------T-Shirt X-------------3--------6--------XXL
3---------T-Shirt Redd--------6--------9---------XXL

I want it to be able to chane it from the Sk -- Like "XXL" or picking "L" So, you only get a list of either XXL or L

QtySold is the total of all orders with the same ID in Table1

If anyone can do this would be great!

Thanks so much.

Regards.

[devinemke]

$result = mysql_query('SELECT * FROM table2') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$table2[$row['ItemId']] = $row;}

$result = mysql_query("SELECT *, SUM(soldQty) AS sum FROM table1 WHERE Sk = 'XXL' GROUP BY ItemId") or exit(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
	echo $row['ItemId'] . '-----' . 
	$table2[$row['ItemId']]['itemName'] . '-----' . 
	$table2[$row['ItemId']]['stock '] . '-----' .
	$row['sum'] . '-----' .
	$row['Sk'];
}

[bogu]

Another solution:

SELECT
	t1.ItemId, t1.itemName, t1.stock,
	SUM(t2.soldQty) as sold, GROUP_CONCAT(DISTINCT t2.Sk SEPARATOR ',') as Sk
FROM table1 t1
LEFT JOIN table t2 USING(ItemId)
GROUP BY t1.ItemId

something like this, is not tested.

BTW, notice that GROUP_CONCAT si not available only from mysql >= 4.1

[errorCode30]

Thanks for your help guys.

Its far too hard for what i know 🙁

Cheers.