Problems with getting the right data ?

[EnjoyNews]

I have some tables that is connected in a table like this:

TracklistID
CDid
SongID

it could look like this
1 1 1
2 1 2
3 1 3
4 1 4
5 2 3
6 2 4
7 2 5
8 3 4

So CD 1 has the four tracks 1,2,3 and 4
CD 2 has three tracks 2,4 and 6
and CD 3 has the one track 4

Then I can list the albums that contains track 3 very simple ofcause by typr
WHERE SongID='3'

But now I want to do a search on which albums contains to different songs.
Ex. which albums contain track 3 and 4
If I type WHERE SongID='3' AND SongID='4' it doesn't work because no posts
has two songid's ofcause.
And if I type WHERE SongID='3' OR SongID='4' it doesn't work because it then
includes CD 3 because it has one of the tracks.

So how do I get it to list the CD's that has both track 3 and 4 in it ??
best
Michael

[Bill Karwin]

"EnjoyNews" <[e-mail]> wrote in message
news:43**********************@dread14.news.tele.dk ...

But now I want to do a search on which albums contains to different songs.
Ex. which albums contain track 3 and 4

Any time you need to compare values in two different records of your table,
one way you can do this by doing a self-join. You need to get two records
from the table into one row of the result set, before you can compare their
values in an expression.

SELECT t1.CDid
FROM trackTable AS t1 INNER JOIN trackTable AS t2 ON t1.CDid = t2.CDid
WHERE t1.SongID = 3 AND t2.SongID = 4

One problem is that if you need to do the same thing with three tracks, or
sixteen tracks, you need to expand your query to do the self-join multiple
times. Many joins in one query has an increasing curve of processing cost,
and there's also a hard limit of 31 joined tables in a query.

Another possible solution is to test for each CD, look for any of the tracks
in question, group by the CD id, and see if the count of matches equals the
number of tracks you're looking for.

SELECT t1.CDid
FROM trackTable AS t1
WHERE t1.SongID IN (3, 4, 5, 6, 7, 8)
GROUP BY t1.CDid
HAVING COUNT(t1.SongID) = 6

This has the advantage of being expandable as the number of tracks you're
looking for changes.

Regards,
Bill K.