angryip angryip - 1 month ago 7
SQL Question

Getting records for distinct results

I have a table (NerdsTable) as follows:

--------------+---------------------+--------------
name school id
--------------+---------------------+--------------
Mike China 5000
Joe Serbia 100
Ana Serbia 100
Rats Khaleesi Land 100
--------------+---------------------+--------------


When I issue a query like this:

select distinct school from dbo.NerdsTable;


I get back results:

[china, serbia, Khaleesi Land]


When I issue a query like this:

select * from dbo.NerdsTable where school = 'china'


I get results back like this:

[Mike, China, 5000]


What I want to do is get a distinct list of schools, and for each school, show the student: Hence, I tried to combine the two queries into something like this:

select * from dbo.NerdsTable where school = (select distinct school from dbo.NerdsTable);


However, I get an error stating that:

Subquery returned more than 1 value.


The error makes sense, but I am unsure as to how I can instruct the sql server query to loop through all distinct results and execute the select * statement.

Answer

You should use in clause

select * from dbo.NerdsTable 
where school in (select  school from dbo.NerdsTable);

because the school for subselect are more then one

Comments