Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2010
    Posts
    6

    Question Unanswered: MORE LEFT JOIN Help needed for Access 2003 .MDB

    NOTE: I reposted this below (post #6 of this message) with code tags for better formatting. You can jump there with this link: http://www.dbforums.com/6449151-post6.html

    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
    Attached Files Attached Files
    Last edited by mikeslavis; 02-22-10 at 11:17.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2010
    Posts
    6
    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

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.

    Code:
    This is            inside code tags
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's an example, just press the Quote button to see the code tags --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2010
    Posts
    6

    Repost of first message with CODE tags for readability

    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:
    Code:
    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:
    Code:
    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:
    Code:
    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:

    Code:
    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:

    Code:
    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:

    Code:
    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:

    Code:
    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
    Last edited by mikeslavis; 02-22-10 at 11:23. Reason: Mis-pasted desired results in original post.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    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.RatingItemTrackID
    WHERE PlaylistItem.PlaylistID=1 Or RatingItem.RatingID Is Null
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2010
    Posts
    6

    Talking Thanks!

    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:

    Code:
    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
    3		1		5		24			6			24			6			1
    									7			11			7			1
    1		1		4		12			8			12			8			1
    ...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.
    Attached Files Attached Files

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •