Better way to write this select?

[cfraz]

Hello all, 1st time post here.

I have a table that has two columns (among others): stream_name and year_survey. There are ~ 150 different values in stream_name, and thousands of rows. In other words, the same stream_name can exist in multiple rows.

Now for some stream_name values, year_survey is the same for all rows of that particular stream_name. For other stream_name values, year_survey is different in each row.

I want to select all rows for each stream_name where the year_survey is not the same for each and every row of that stream_name.

Here is what I've written using nested sub selects and grouping, and it works, but it seems uber ugly to me. There must be a better way to do this.

MySQL 5.0.18.

SELECT stream_name, year_survey FROM
(SELECT stream_name, year_survey FROM survey
GROUP BY stream_name, year_survey) WHERE stream_name IN
(SELECT stream_name FROM
(SELECT stream_name, year_survey FROM survey
GROUP BY stream_name, year_survey)
GROUP BY stream_name HAVING COUNT(stream_name) > 1)