Transact-SQL Help

[Guest_imported]

Please help! I have a table called MusicInstrument that is a one too many relationship. Each Song ID could have 1 or more instruments linked to the Song ID. When I do a select I want to order it by the songs that only have one instrument associated with that song and then all the song that have more than one instrument. For example I want to select all the songs that have the instrument Piano. So I want the Songs that just have piano and not the songs that have piano, bass, and sax in that song. I know this might be confusing but if you understand where I am getting at and might have a soultion I would greatly appreciate it. Thanks for you help.

Sanjay

MY Table looks like this:

SONGID INSTRUMENT
1 Piano
2 Piano
2 Sax
2 Bass

 

[jhall156]

SELECT a.SongId,
b.SongName,
COUNT(a.Instrument) AS NumInst
FROM SongsAndInstruments a
INNER JOIN
Songs b ON a.SongID = b.SongID
GROUP BY a.SongID
ORDER BY NumInst

The result set will contain a list of song IDs, the names of the songs and the number of instruments in each song sorted by the number of instruments in ascending order

 

[tlbroadbent]

Query to find songs featuring only the piano.

Select * from Songs
Where SongID In
(Select SongID From Songs
Group By SongID
Having count(*)=1)
And Instrument='piano'

Query to order songs by number of instruments involved.

Select * From Songs As s
Order By
(Select Count(*) From Songs
Where SongID=s.SongId
Group By SongID),
SongID, Instrument Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.