CODEWITHSUNDEEP
sqlsql-serverselect
tucosalamanca
tucosalamanca

Reputation: 11

sql query for getting items not rated by both users

Say I have the following table:

+------------+------+--------+
| reviewerID | item | rating |
+------------+------+--------+
|          1 |    1 |       5|
|          1 |    2 |       5|
|          1 |    3 |       5|
|          2 |    4 |       5|
|          2 |    1 |       5|
|          2 |    2 |       5|
+------------+------+--------+

And I want to get the items not rated by reviewer 1 but rated by reviewer 2 and vice versa into one table. The output should be something like this:

+------------+------+--------+
| reviewerID | item | rating |
+------------+------+--------+
|          1 |    3 |       5|
|          2 |    4 |       5|
+------------+------+--------+

Upvotes: 1

Views: 83

Answers (2)

Bikkar
Bikkar

Reputation: 77

Here's what you need, to get the desired results....

SELECT a.* FROM Reviewer a  
JOIN (  SELECT DISTINCT item FROM Reviewer
        GROUP BY item
        HAVING count(item) < 2) b  
ON a.item = b.item

Hope it helps!! Good luck!!

Upvotes: 0

Mureinik
Mureinik

Reputation: 311228

You could count the number of reviewers the items had (between those two reviewers) and only select those with one reviewer:

SELECT *
FROM   mytable
WHERE  item IN (SELECT   item
                FROM     mytable
                WHERE    reviewerID IN (1, 2)
                GROUP BY item
                HAVING   COUNT(*) = 1)

Upvotes: 1

Related Questions