Group and subtract two tables?

HikaruBunny

Hi guys~! What I am trying to do is take two tables group them, count them then subtract the results. I think I may need a table join but I am not sure? :p Here is what my data looks like.

Table 1:

id | user_id | item_id | item_name

1 | 2 | 1 | apple
2 | 2 | 1 | apple
3 | 2 | 2 | orange
4 | 2 | 3 | banana
5 | 2 | 3 | banana

When grouped and counted is:

apple 2
orange 1
banana 2

Table 2: (needs to be subtracted from table 1)

id | user_id | item_id | item_name

1 | 2 | 1 | apple
3 | 2 | 2 | orange
4 | 2 | 3 | banana

When grouped and counted is:

apple 1
orange 1
banana 1

The information I am trying to get should be:

item_name | amount

apple | 1
orange | 0
banana | 1

Here is my current function that will count and group one table:

function doReadItem($id) {
		$sql = sprintf("SELECT *, count(item_name) as amount 
		FROM user_items
		WHERE user_id = $id
		GROUP BY item_name");
		$result = mysql_query($sql);
		return $result;
	}

A push in the right direction would be soooo nice~ :quiet:

tomhath

First thing that comes to mind is to use two subselects:

select (t1.amount - t2.amount) as amount, t1.item_name

from (SELECT count(item_name) as amount, item_name
         FROM user_items
         WHERE user_id = $id
         GROUP BY item_name) as t1

inner join (SELECT count(item_name) as amount, item_name
             FROM user_items_2
             WHERE user_id = $id
             GROUP BY item_name) as t2 

on t2.item_name = t1.item_name

Lars_Berg

select item_name, count(*) - 
     (select count(*)
          from t2
         where t2.item_id = t1.item_id)
   from t1
  group by item_name

tomhath

SELECT count(distinct t1.id) - count(distinct t2.id) as amount, t1.item_name
FROM user_items1 t1
INNER JOIN user_items2 t2 ON t2.item_id = t1.item_id
WHERE t1.user_id = $id and t2.user_id = $id
GROUP BY t1.item_name