Confusing query problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Confusing query problem

Author

Topic

mark-o
Starting Member

1 Post

Posted - 2008-06-11 : 19:44:58

I am looking to query one table where the matching information in the related table needs to be a subset of the data - but not always a complete set (and there may be added information in the related table). For example, if I have a table of Albums and I want to match songs to albums as follows:

Albums

Name CHAR_TYPE CHAR
Rock guitarists Genre Rock
Rock guitarists Instrument Guitar
Great Guitar Instrument Guitar
Rock and Blues Genre Rock
Rock and Blues Genre Blues

Songs

Name CHAR_TYPE CHAR
Purple Haze Genre Rock
Purple Haze Instrument Guitar
Purple Haze Artist Hendrix
Red House Genre Blues
Red House Instrument Guitar
Red House Artist Hendrix
Stairway to heaven Genre Rock
Stairway to heaven Instrument Guitar

For this query, the results should be

Album Song
Rock guitarists Purple Haze & Staiway to Heaven
Great Guitar All 3
Rock and Blues All 3

Everything I've tried either incudes extra songs (Rock Guitar includes Red House) OR Eliminates entire albums (everything else works, but rock and Blues produces no results

Thanks in advance for any sugestions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 00:57:10

Not clear from your sample data the rules for matching. Can you elaborate?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources