0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people interested in governments, policies, and political processes

;WITH items (i_id, item_id, user_id) AS (
	SELECT 1, 1, 11 UNION ALL
	SELECT 2, 2, 12 UNION ALL
	SELECT 3, 3, 13
)
, purchases (p_id, item_id, user_id, rating) AS (
	SELECT 1, 1, 13, -1 UNION ALL
	SELECT 2, 2, 11, 1 UNION ALL
	SELECT 3, 3, 12, NULL UNION ALL
	SELECT 4, 1, 14, -1
)
, collaborators (c_id, item_id, user_id) AS (
	SELECT 1, 1, 12 UNION ALL
	SELECT 2, 2, 13
)
SELECT u.user_id, pt.item_id, pt.cnt, pt.yes, pt.no
FROM   (
	    SELECT user_id, item_id FROM items
	    UNION SELECT user_id, item_id FROM purchases
	    UNION SELECT user_id, item_id FROM collaborators	    
	   ) u INNER JOIN (
        SELECT COUNT(*) AS cnt
               , SUM(CASE WHEN rating = 1 THEN 1 ELSE 0 END) AS yes
               , SUM(CASE WHEN rating =-1 THEN 1 ELSE 0 END) AS no
               , item_id
        FROM   purchases
        GROUP BY
               item_id
      ) pt ON pt.item_id = u.item_id

fork query

created jun 20 12 anonymous