find all rows where one row matches criteria (1 Viewer)

[GS500]

| ReviewID | QstnID | Rating |
|:--------:|:------:|:------:|
|     1    |    1   |    0   |
|     1    |    2   |    1   |
|     1    |    3   |    1   |
|     1    |    4   |    2   |
|     1    |    5   |    2   |
|     2    |    1   |    1   |
|     2    |    2   |    1   |
|     2    |    3   |    1   |
|     2    |    4   |    2   |
|     2    |    5   |    2   |

I have data in a table that shows as above. What I need to do is find all ReviewIDs where any of the Ratings show 0. So the above dataset should return all rows with a ReviewID of 1, since there is a 0 Rating for that Review. I think I would do this with a subquery, and I've tried a few I found on different forums, but they only return the rows with the 0 Rating, not all the rows of the matching ReviewID.

Additionally, once I get the correct results, I need to change the other ratings for that Review to a 0 also.

 

[bob fitz]

Create a query with criteria which will return the one record.
Create another query using the first query and the original table joined by the ReviewID field in each

 

[GS500]

Well I was definitely making that harder than it needed to be! And then I can make the 2nd query an update query to change the ratings to 0.

Thanks bob!

 

[bob fitz]

Glad to help.
Perhaps you could mark the thread as "Solved" if you feel your question has been answered

 

[MarkK]

You can also use the IN operator...

SELECT *
FROM Table1
WHERE ReviewID IN 
   ( 
   SELECT ReviewID 
   FROM Table1 
   WHERE Rating = 0
   )

...or, as Bob suggests...

SELECT t.*
FROM Table1 as t INNER JOIN 
   ( 
   SELECT ReviewID 
   FROM Table1 
   WHERE Rating = 0 
   ) As q
ON t.ReviewID = q.ReviewID

My bet would be the INNER JOIN would be faster on big data sets, but I'm totally guessing at that.

But to do the update you would never have to write that select query either. You could just do...

UPDATE Table1
SET Rating = 0
WHERE ReviewID IN 
   ( 
   SELECT ReviewID 
   FROM Table1 
   WHERE Rating = 0
   )

 

[GS500]

Thanks MarkK, I like your solution for the update, gets it all in one query! Keeps my navigation pane a little cleaner.