SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help needed with this SQL statement

    I've got one last piece to this SQL statement that I can't quite figure out. I'm trying to do a union between three tables with the following characteristics.

    tblArtists - one to many
    tblAlbums - each Artist can have one to many albums
    tblTracks - each Album can have one to many tracks

    What I want to do is list data by Artist and Album, where the lyrics id for an album track is a certain value.

    The following query works, but with the literal Where value just as you see it here (bolded for emphasis). I want to know what the Where clause should really look like.

    Thanks in advance for your help.

    Code:
    (
    SELECT 
    tblAlbums.Title AS albumtitle
    , tblAlbums.TitleSort
    , tblAlbums.MediaType AS format
    , tblAlbums.AlbumID AS albumid
    , tblArtists.The AS artist_the
    , tblArtists.Artist AS artist
    , tblArtists.ArtistID AS artistid
    , tblArtists.SortName AS sortname
    FROM tblArtists
    INNER JOIN tblAlbums ON tblArtists.ArtistID = tblAlbums.ArtistID
    WHERE tblArtists.Artist LIKE '%" . $this->where . "%'
    )
    UNION (
    
    SELECT 
    tblAlbums.Title
    , tblAlbums.TitleSort
    , tblAlbums.MediaType
    , tblAlbums.AlbumID
    , tblArtists.The
    , tblArtists.Artist
    , tblArtists.ArtistID
    , tblArtists.SortName
    FROM tblTracks
    INNER JOIN tblAlbums ON tblTracks.AlbumID = tblAlbums.AlbumID
    INNER JOIN tblArtists ON tblAlbums.ArtistID = tblArtists.ArtistID
    WHERE tblTracks.Lyrics =30
    )
    ORDER BY sortname, albumtitle
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you please show a few rows of sample data from each table, using data values that actually relate the rows across the tables, and then indicate what you want the result of your query to produce
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll do my best.

    Record 1 from tblArtists
    tblArtists.The - null
    tblArtists.Artist - B. W. Stevenson
    tblArtists.ArtistID - 83
    tblArtists.SortName - stevenson b w

    Record 2 from tblArtists
    tblArtists.The - null
    tblArtists.Artist - John Denver
    tblArtists.ArtistID - 81
    tblArtists.SortName - denver john


    Record 1 from tblAlbums
    tblAlbums.Title - Greatest Hits
    tblAlbums.TitleSort - greatest hits
    tblAlbums.MediaType - CD
    tblAlbums.AlbumID - 222
    tblAlbums.ArtistID - 81

    Record 2 from tblAlbums
    tblAlbums.Title - Lifeline
    tblAlbums.TitleSort - lifeline
    tblAlbums.MediaType - CD
    tblAlbums.AlbumID - 225
    tblAlbums.ArtistID - 83


    Record 1 from tblTracks
    tblAlbums.AlbumID - 225
    tblAlbums.Lyrics - 30

    Record 2 from tblTracks
    tblAlbums.AlbumID - 222
    tblAlbums.Lyrics - null

    I only want to display artists and albums where any album track has
    tblTracks.Lyrics = 30

    So the result I want from this database record list is:

    tblArtists.The - null
    tblArtists.Artist - B. W. Stevenson
    tblArtists.ArtistID - 83
    tblArtists.SortName - stevenson b w
    tblAlbums.Title - Lifeline
    tblAlbums.TitleSort - lifeline
    tblAlbums.MediaType - CD
    tblAlbums.AlbumID - 225
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i just wanted to comment on your SortName column, that is incredibly sexy and i really like the thinking behind it

    nice job

    okay, about your query, where did you come up with UNION?

    i'm not sure i fully understand what you're trying to get, if it's just artists and albums where somewhere on the album is a track which you identify by Lyrics=30, that sounds a lot like a simple 3-table join with a DISTINCT...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What I'm trying to end up with is something similar to what you helped me with several months ago in this thread, except this time I want an artist and album list where the lyrics field (from the tracks table) has a certain value. I thought I could just modify that query a little bit to get what I want, but I'm having a heck of a time doing it.

    Hope this explains a little better what I'm trying to accomplish.

    <edit>
    Little bit more information:

    Lyrics id is on the tracks table, but I only want to display data from the artists and albums tables. Otherwise, each artist/album combination will be printed multiple times.
    </edit>
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  6. #6
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I don't understand why this works and why it absolutely requires the Where clause, even though the Where clause is just garbage thrown in there, but I got the query to work like this:

    Code:
    (SELECT 
    tblAlbums.Title AS albumtitle
    , tblAlbums.TitleSort
    , tblAlbums.MediaType AS format
    , tblAlbums.AlbumID AS albumid
    , tblArtists.The AS artist_the
    , tblArtists.Artist AS artist
    , tblArtists.ArtistID AS artistid
    , tblArtists.SortName AS sortname
    FROM tblArtists
    INNER JOIN tblAlbums ON tblArtists.ArtistID = tblAlbums.ArtistID
    WHERE tblAlbums.Artist LIKE '%anything%'
    )
    UNION (
    
    SELECT 
    tblAlbums.Title
    , tblAlbums.TitleSort
    , tblAlbums.MediaType
    , tblAlbums.AlbumID
    , tblArtists.The
    , tblArtists.Artist
    , tblArtists.ArtistID
    , tblArtists.SortName
    FROM tblTracks
    INNER JOIN tblAlbums ON tblTracks.AlbumID = tblAlbums.AlbumID
    INNER JOIN tblArtists ON tblAlbums.ArtistID = tblArtists.ArtistID
    WHERE tblTracks.Lyrics =30
    )
    ORDER BY sortname, albumtitle
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the query in the other thread you cited used UNION because there were two different paths to take to get the desired results

    in this case, there's only one, so you don't want to use UNION
    Code:
    select distinct
           tblAlbums.Title
         , tblAlbums.TitleSort
         , tblAlbums.MediaType
         , tblAlbums.AlbumID
         , tblArtists.The
         , tblArtists.Artist
         , tblArtists.ArtistID
         , tblArtists.SortName
      from tblAlbums 
    inner 
      join tblArtists 
        on tblAlbums.ArtistID 
         = tblArtists.ArtistID
    inner
      join tblTracks
        on tblAlbums.AlbumID 
         = tblTracks.AlbumID
       and tblTracks.Lyrics = 30
    order
        by tblArtists.SortName
         , tblAlbums.Title
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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