[RESOLVED] Multi join??

[ehask71]

Ok I have no hair left and I don't know why I am drawing a blank on this but

I have 2 tables

Item (item_id,item_name)

Reviews (item_id,account_id,positive (enum Y, N), comment)

Primary Key is item_id in both tables

I want to get Item name and the count of how many users gave it a positive review and count of how many gave it a negative review and the total reviews on items that have been reviewed at least 3 times.

SELECT item, count( * ) AS total
FROM items
INNER JOIN reviews
USING ( item_id )
GROUP BY item_id
HAVING total >2

Is the query I am currently using to get the items that have been
reviewed more than 3 times.

Thx for any help

[laserlight]

Primary Key is item_id in both tables

That implies that an item can have no more than one review. Consequently, no item can be reviewed three times, so what you want to select will always be the null set. Rather, I think that the primary key for Reviews should be a combination of the item_id and account_id.

[greensweater]

select item_id, sum(good) as good, sum(bad) as bad, sum(good)+sum(bad) as total from 
(select a.item_id, count(positive) as good, 0 as bad 
  from items a, reviews b where a.item_id=b.item_id and positive = 'y' 
  group by a.item_id
union
select a.item_id, 0 as good, count(positive) as bad 
  from items a, reviews b where a.item_id=b.item_id and positive = 'n'  

  group by a.item_id) as totals
group by item_id
having total>2

(assuming item_id is not primary key of reviews table)