Select distinct statement SQL
Posted on 2010-09-01
This SQL statement returns multiple rows that are duplicate, where I want it to only list values once:
"SELECT * FROM Instrument Left Outer Join Schedule On Instrument.InstrumentID = Schedule.InstrumentID Where Schedule.InstrumentID IN (SELECT MAX(Schedule.InstrumentID) FROM Schedule WHERE Instrument.InstrumentID = Schedule.InstrumentID Group by Schedule.InstrumentID )"
The 2 tables are:
I want the SQL to return Instrument name from Instrument table, only when it occurs in the Schedule table. And if it occurs multiple times in Schedule table, only return it once, and not more than once.
So cello, violin and piano will be selected only once, and guitar will not be selected at all. Currently, the above SQL selects the instrument more than once, for example it selects violin 3 times, cello once, and piano 2 times.