Consulting

Results 1 to 2 of 2

Thread: Help Please - Sql Query

  1. #1

    Help Please - Sql Query

    My database is not very well built, but I'm stuck with it for now. I'm in a desperate rush to get this info out of my database, and I need help writing the query or queries to get it to work!! Greatly appreciate any help!

    What I want to see at the end of this as a single result set, in plain English:
    Songtitle ID, Songtitle, Songwriter, Song Description, Mood 1, Mood 2, Mood 3, Genre 1, Genre 2, Genre 3.


    From my songs table I need the following fields —
    BUT, only the id and the song title fields definitely have data in them and I need to see the id and song title whether or not there is data in any of the other fields.
    t_songs2.ID_songs, t_songs2.Songtitle, t_songs2.songdescription, t_songs2.mood_main_fk, t_songs2.mood_2_fk, t_songs2.mood_3_fk, t_songs2.songwriters

    The moods lookup a value in dd_moods. I want to see the groupname instead of the foreign key in the query above. the mood fk as above is an fk to ID_descripgroup.
    dd_moods has these two fields:
    ID_descripgroup and groupname

    Once that gets pulled together, I need to add three more fields to that result set.

    I need the three genres for each song, whether or not those songs have genres assigned yet. The song table and the genre tables are joined with a join table, and like the mood table there is a lookup table for the name of the genre. The tables involved are: dd_genre as the lookup table with the fields ID_genre and musictype; and j_song_genre that connects up to three genres for each song.

    OMG -- my head is whirling! Probably a no-brainer for most of you, but I'm spending like a day on this already and have decided to ask the calvary! Thank you!!!

  2. #2
    VBAX Regular JBeaucaire's Avatar
    Joined
    Sep 2014
    Location
    Bakersfield
    Posts
    32
    Location
    Not following this exactly, yet. If you do a straight JOIN, then things disappear from the results when missing from one of the joined tables, which is desired behavior in many queries, but not this case? So, to keep that from happening do a LEFT JOIN. This insures items that are in the lefthand table are included even if there is no match to the right hand table.

    Simplest starting point;

    SELECT * FROM t_songs2 t WITH(NOLOCK) 
      JOIN dd_moods dd WITH(NOLOCK) ON t.mood_main_fk = dd.ID_descripgroup
    Then:
    SELECT * FROM t_songs2 t WITH(NOLOCK) 
      LEFT JOIN dd_moods dd WITH(NOLOCK) ON t.mood_main_fk = dd.ID_descripgroup
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

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
  •