NOTE: I reposted this below (post #6 of this message) with code tags for better formatting. You can jump there with this link: MORE LEFT JOIN Help needed for Access 2003 .MDB

DBForum gurus, thanks for the help on the last LEFT JOIN issue I asked about, now I've "taken it to the next level" and am stuck once again. I'll try to keep this simple and I'll attach the sample DB I've put together.

The dilemma: I have a master "Song" table, plus separate "Playlist" and "Rating" tables. The Playlist table is for defining a list of songs to be played in order. A song can exist in a playlist multiple times. The Rating table is for assigning a 0 - 5 style rating system to songs. A song can only be in 1 rating, but there can be several Rating "Masters" defined (perhaps Mike wants to assign different ratings than Suzi). OK, here are some table data snippets before I get to what I'm looking to do:

SONG:
SongID SongName SongMinutes SongSeconds ArtistID
1 Straight 2 32 1
2 Volcano Girls 3 18 1
3 Don't Make Me Prove It 2 29 1
4 Awesome 3 32 1
5 One Last Time 4 45 1
6 With David Bowie 2 25 1
7 Benjamin 4 5 1
8 Shutterbug 4 16 1
9 The Morning Sad 3 8 1
10 Sound Of The Bell 3 58 1
11 Loneliness Is Worse 5 0 1
12 Stoneface 2 44 1
13 Venus Man Trap 3 29 1
14 Earthcrosser 5 28 1
15 Helpless 6 38 2
16 The Small Hours 6 43 2
17 The Wait 4 55 2
18 Crash Course In Brain Surgery 3 10 2
19 Last Caress/Green Hell 3 29 2
20 Am I Evil? 7 50 2
21 Blitzkrieg 3 36 2
22 Breadfan 5 41 2
23 The Prince 4 25 2
24 Stone Cold Crazy 2 17 2
25 So What 3 8 2

PlaylistItem:
PlaylistItemID PlaylistID PlaylistItemOrdinal PlaylistItemTrackID
1 1 1 10
2 1 2 11
3 1 3 12
4 1 4 22
5 1 5 23
6 1 6 24
7 1 7 11
8 1 8 12
9 2 1 10
10 2 2 11
11 2 3 12

RatingItem:
RatingItemID RatingID RatingValue RatingItemTrackID
1 1 4 12
2 2 3 12
3 1 5 24
4 2 5 23

...these are in the attached DB with data.

Here's what I'd like to do: Grab back *all* the PlaylistItems for PlaylistID = 1 and also any RatingItems where the RatingItemTrackID is a match on the PlaylistItemTrackID and the RatingID = 1 (or NULLS for the Rating values where there's no match). I haven't been able to actually do this yet, but here's what that query result *should* look like:

RatingItemID RatingID RatingValue RatingItemTrackID PlaylistItemOrdinal PlaylistItemTrackID PlaylistItemID PlaylistID
1 10 1 1
2 11 2 1
1 1 4 12 3 12 3 1
4 22 4 1
5 23 5 1 <--Problem row
3 1 5 24 6 24 6 1
7 11 7 1
1 1 4 12 8 12 8 1

If someone can help me (once again) with some SQL magic, I'd be greatly appreciative. Here are the queries I've tried so far while attempting to figure this out (All in the sample DB as well):

1) Playlist-PlaylistOnly-NoRating: This one returns the Playlist items I want but with NO rating information:

SELECT PlaylistItem.PlaylistItemOrdinal, PlaylistItem.PlaylistItemTrackID, PlaylistItem.PlaylistItemID, PlaylistItem.PlaylistID
FROM PlaylistItem
WHERE (((PlaylistItem.PlaylistID)=1))
ORDER BY PlaylistItem.PlaylistItemOrdinal, PlaylistItem.PlaylistItemTrackID, PlaylistItem.PlaylistItemID;

Results:

PlaylistItemOrdinal PlaylistItemTrackID PlaylistItemID PlaylistID
1 10 1 1
2 11 2 1
3 12 3 1
4 22 4 1
5 23 5 1
6 24 6 1
7 11 7 1
8 12 8 1

2) Playlist-LeftJoin-PlaylistIDOnly: This one returns too many items due to tracks being rated for both RatingIDs 1 and 2 and no filter on RatingID:

SELECT RatingItem.RatingItemID, RatingItem.RatingID, RatingItem.RatingValue, RatingItem.RatingItemTrackID, PlaylistItem.PlaylistItemOrdinal, PlaylistItem.PlaylistItemTrackID, PlaylistItem.PlaylistItemID, PlaylistItem.PlaylistID
FROM PlaylistItem LEFT JOIN RatingItem ON PlaylistItem.PlaylistItemTrackID = RatingItem.RatingItemTrackID
WHERE (((PlaylistItem.PlaylistID)=1))
ORDER BY PlaylistItem.PlaylistItemOrdinal, PlaylistItem.PlaylistItemTrackID, PlaylistItem.PlaylistItemID;

Results:

RatingItemID RatingID RatingValue RatingItemTrackID PlaylistItemOrdinal PlaylistItemTrackID PlaylistItemID PlaylistID
1 10 1 1
2 11 2 1
2 2 3 12 3 12 3 1
1 1 4 12 3 12 3 1
4 22 4 1
4 2 5 23 5 23 5 1
3 1 5 24 6 24 6 1
7 11 7 1
2 2 3 12 8 12 8 1
1 1 4 12 8 12 8 1

3) Playlist-LeftJoin-RatingID-NotWorking: This one filters on RatingID = 1 or IS NULL, but doesn't work becase TrackID 5 has been rated in RatingID 2, so it's not = RatingID 1 or NULL, and gets left out of the return:

SELECT RatingItem.RatingItemID, RatingItem.RatingID, RatingItem.RatingValue, RatingItem.RatingItemTrackID, PlaylistItem.PlaylistItemOrdinal, PlaylistItem.PlaylistItemTrackID, PlaylistItem.PlaylistItemID, PlaylistItem.PlaylistID
FROM PlaylistItem LEFT JOIN RatingItem ON PlaylistItem.PlaylistItemTrackID = RatingItem.RatingItemTrackID
WHERE (((RatingItem.RatingID)=1 Or (RatingItem.RatingID) Is Null) AND ((PlaylistItem.PlaylistID)=1))
ORDER BY PlaylistItem.PlaylistItemOrdinal, PlaylistItem.PlaylistItemTrackID, PlaylistItem.PlaylistItemID;

Results:

RatingItemID RatingID RatingValue RatingItemTrackID PlaylistItemOrdinal PlaylistItemTrackID PlaylistItemID PlaylistID
1 10 1 1
2 11 2 1
1 1 4 12 3 12 3 1
4 22 4 1
3 1 5 24 6 24 6 1
7 11 7 1
1 1 4 12 8 12 8 1

...This is where I'm stuck. What I'm doing now that is working, but seems inefficient to me, is to execute query 1, independent of Rating information, then use a VB6 Collection object to store the results, using the PlaylistItemID (Uniqie for each row) as the key to the obejcts in the collection. After this is complete, I execute a 2nd query like query 3 listed above except I pull out the IS NULL condition so I only pull back items actually assigned to RatingID = 1. I then update my collection, by key, adding in the Rating information. Another method I could use, but I'm not currently, is to execute the query in 2) above, and as I loop through the Recordset, "throw away" any rows that are not RatingID = 1 or NULL. This would seem to get rather wasteful as the DB gets larger, so I'm opting not to do it this way.

Any help on trying to get this done with 1 SQL call? Can it be done? Thanks for listening....

Mike

Hi

Please can you use code tags and line your data up so it can be easily read. Your required results are very difficult to read.

pootle flump ur codings are working excelent.

pootle or someone else familiar with code tags, if you could provide me with just 1 quick example of how a code tag works, I'll reformat the tables in my original message to use them. I've looked for about 15 minutes and can't find a good example anywhere, and the tries I've done, like:

<Code>
<Table>
<TR>
<TD>
Test column 1
</TD>
<TD>
Test column 2
</TD>
<TD>
Test column 3
</TD>
</TR>
</Table>
</Code>

...don't do what I think they should. Sorry I can't figure this out, I just need 1 quick example (or a link to some examples/manual/help of any kind on it) and I'll take it from there.

Thanks,

Mike

Ah - no - don't write HTML!

[code ][/code ] (without the spaces). Anything you paste in there will have formatting (e.g. spaces, tabs etc) preserved so you can line stuff up in to columns.

pootle flump ur codings are working excelent.

here's an example, just press the Quote button to see the code tags --

NOTE: This is a repost of my original message, with sample table data reformatted using code tags for readability. The sample database I attached to the very first post of this message contains all the sample tables with data, as well as the query attempts I discuss below.

DBForum gurus, thanks for the help on the last LEFT JOIN issue I asked about, now I've "taken it to the next level" and am stuck once again. I'll try to keep this simple and I'll attach the sample DB I've put together.

The dilemma: I have a master "Song" table, plus separate "Playlist" and "Rating" tables. The Playlist table is for defining a list of songs to be played in order. A song can exist in a playlist multiple times. The Rating table is for assigning a 0 - 5 style rating system to songs. A song can only be in 1 rating, but there can be several Rating "Masters" defined (perhaps Mike wants to assign different ratings than Suzi). OK, here are some table data snippets before I get to what I'm looking to do:

SONG:
PlaylistItem:
RatingItem:
...these are in the attached DB with data.

Here's what I'd like to do: Grab back *all* the PlaylistItems for PlaylistID = 1 and also any RatingItems where the RatingItemTrackID is a match on the PlaylistItemTrackID and the RatingID = 1 (or NULLS for the Rating values where there's no match). I haven't been able to actually do this yet, but here's what that query result *should* look like:

If someone can help me (once again) with some SQL magic, I'd be greatly appreciative. Here are the queries I've tried so far while attempting to figure this out (All in the sample DB as well):

1) Playlist-PlaylistOnly-NoRating: This one returns the Playlist items I want but with NO rating information:

SELECT PlaylistItem.PlaylistItemOrdinal, PlaylistItem.PlaylistItemTrackID, PlaylistItem.PlaylistItemID, PlaylistItem.PlaylistID
FROM PlaylistItem
WHERE (((PlaylistItem.PlaylistID)=1))
ORDER BY PlaylistItem.PlaylistItemOrdinal, PlaylistItem.PlaylistItemTrackID, PlaylistItem.PlaylistItemID;

Results:

2) Playlist-LeftJoin-PlaylistIDOnly: This one returns too many items due to tracks being rated for both RatingIDs 1 and 2 and no filter on RatingID:

SELECT RatingItem.RatingItemID, RatingItem.RatingID, RatingItem.RatingValue, RatingItem.RatingItemTrackID, PlaylistItem.PlaylistItemOrdinal, PlaylistItem.PlaylistItemTrackID, PlaylistItem.PlaylistItemID, PlaylistItem.PlaylistID
FROM PlaylistItem LEFT JOIN RatingItem ON PlaylistItem.PlaylistItemTrackID = RatingItem.RatingItemTrackID
WHERE (((PlaylistItem.PlaylistID)=1))
ORDER BY PlaylistItem.PlaylistItemOrdinal, PlaylistItem.PlaylistItemTrackID, PlaylistItem.PlaylistItemID;

Results:

3) Playlist-LeftJoin-RatingID-NotWorking: This one filters on RatingID = 1 or IS NULL, but doesn't work becase TrackID 5 has been rated in RatingID 2, so it's not = RatingID 1 or NULL, and gets left out of the return:

SELECT RatingItem.RatingItemID, RatingItem.RatingID, RatingItem.RatingValue, RatingItem.RatingItemTrackID, PlaylistItem.PlaylistItemOrdinal, PlaylistItem.PlaylistItemTrackID, PlaylistItem.PlaylistItemID, PlaylistItem.PlaylistID
FROM PlaylistItem LEFT JOIN RatingItem ON PlaylistItem.PlaylistItemTrackID = RatingItem.RatingItemTrackID
WHERE (((RatingItem.RatingID)=1 Or (RatingItem.RatingID) Is Null) AND ((PlaylistItem.PlaylistID)=1))
ORDER BY PlaylistItem.PlaylistItemOrdinal, PlaylistItem.PlaylistItemTrackID, PlaylistItem.PlaylistItemID;

Results:

...This is where I'm stuck. What I'm doing now that is working, but seems inefficient to me, is to execute query 1, independent of Rating information, then use a VB6 Collection object to store the results, using the PlaylistItemID (Uniqie for each row) as the key to the obejcts in the collection. After this is complete, I execute a 2nd query like query 3 listed above except I pull out the IS NULL condition so I only pull back items actually assigned to RatingID = 1. I then update my collection, by key, adding in the Rating information. Another method I could use, but I'm not currently, is to execute the query in 2) above, and as I loop through the Recordset, "throw away" any rows that are not RatingID = 1 or NULL. This would seem to get rather wasteful as the DB gets larger, so I'm opting not to do it this way.

Any help on trying to get this done with 1 SQL call? Can it be done? Thanks for listening....

Mike

pootle flump ur codings are working excelent.

Pootle,

THANKS again big time for the help. Note, though, that the query you show above doesn't exactly do what I was looking for, although it "unlocks the door" for me, and with a simple change I can get exactly what I want. Changing your query to:

SELECT RatingItem.RatingItemID, RatingItem.RatingID, RatingItem.RatingValue, RatingItem.RatingItemTrackID, PlaylistItem.PlaylistItemOrdinal, PlaylistItem.PlaylistItemTrackID, PlaylistItem.PlaylistItemID, PlaylistItem.PlaylistID
FROM PlaylistItem LEFT JOIN (SELECT * FROM RatingItem WHERE RatingItem.RatingID=1) AS RatingItem ON PlaylistItem.PlaylistItemTrackID=RatingItem.Rating ItemTrackID
WHERE PlaylistItem.PlaylistID=1;

Yields the result:

...which is exactly what I was looking for. I'm learning through your help as well...looks like the subquery pulls out the 2 records from the RatingItem table for RatingID 1, calls this resultant "table" RatingItem and the LEFT JOIN then "adds" those results into the main query where the PlaylistItemTrackID = RatingItemTrackID, which successfully adds the 3 entries (since Rated Track 12 is twice in the Playlist) and NULLS for the Ratings for the rest. Wonderful....Maybe some day, I'll ACTUALLY be able to come up with stuff like this on my own.

Thanks again,

Mike

P.S. I'm including a .ZIP of the sample table with the successful query saved as PlaylistRating-WORKING.

If you are interested, google "derived table sql" for more info. Although the query builder is great, one of the disadvantages is that it makes it difficult for people to learn to write SQL. Derived tables are similar are rarely seen in Access SQL. More commonly a developer would create the derived table as a totally separate query and join to that, all in the query builder GUI.

pootle flump ur codings are working excelent.