DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 27

Thread: SQL Query Help

  1. #1
    Join Date
    Nov 2007
    Posts
    7

    SQL Query Help

    Hi,

    Any tips on how can I get all rows given the following example?

    Table:
    Instruments (contains instruments I own)

    Records:
    Piano
    Guitar
    Keyboard
    Flute
    Violen

    Table:
    Songs (a library of all my songs)

    Records:
    Paradise City (id 1)
    Back In Black (id 2)

    Table:
    Instruments_In_Song (rows for each instrument in each song from above
    table)
    Guitar (song_id 1)
    Keyboard (song_id 1)
    Guitar (song_id 2)
    Drums (song_id 2)

    Forget about the accuracy of the actual example. What I want is to find
    all songs that have instruments that I own. In the example above, the
    query would include Paradise City since I have a guitar and a keyboard,
    but would omit Back in Black since I have a guitar, but don't have a
    drum set.

    Any pointers with SQL would be helpful.

    Thank you,

    Michael

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Cool. Show us your best effort and somebody will point you in the right direction, if needed.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2007
    Posts
    7
    PAVB,

    My best efforts haven't resulted in the right solution! :-( I have never had to do anything like this before.

    The best thing I came up with (from an example in some of these threads) was to change my data model. My Instrument library now is just an ID to the Instruments_In_Song table. The Song table is changed to have a primary key for song name and instrument id. Therefore, each song may have multiple entries, but only one for each instrument. Then, the following query:

    select distinct song_name from songs as S1
    where not exists
    (
    select * from instruments
    where not exists
    (
    select * from songs as S2
    where
    (
    S1.song_name = S2.song_name
    )
    and
    (
    S2.instrument_nbr = instruments.instrument_nbr
    )
    )
    )


    However, it doesn't give me the right results. It will only give me songs that contain every instrument that I own. If I add just one more instrument to my collection and it isn't in the song then I get no results. I want ALL songs that have instruments that are in my collection - even if the song only has one instrument and I have 50 - if that one is in the 50 then I want the song to show up.

    I sure appreciate any help you can provide.

    Regards,

    Michael

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by Michael M.
    I want ALL songs that have instruments that are in my collection - even if the song only has one instrument and I have 50 - if that one is in the 50 then I want the song to show up.
    Michael --

    Your initial data model wasn't that bad.

    Following query uses original data model and if I'm not mistaken it returns what you want...
    Code:
    create table instruments
    (inst varchar2(20))
    ;
    insert into instruments values('Piano');
    insert into instruments values('Guitar');
    insert into instruments values('Keyboard');
    insert into instruments values('Flute');
    insert into instruments values('Violen');
    commit;
    
    create table songs
    (id number,
     song varchar2(20))
    ;
    insert into songs values(1,'Paradise City');
    insert into songs values(2,'Back In Black');
    insert into songs values(3,'Horrible Night');
    commit;
    
    create table inst_in_song
    (inst varchar2(20),
     id   number)
    ;
    insert into inst_in_song values('Guitar',1);
    insert into inst_in_song values('Keyboard',1);
    insert into inst_in_song values('Guitar',2);
    insert into inst_in_song values('Drums',2);
    insert into inst_in_song values('Citar',3);
    commit;
    
    select  distinct
            s.song
    from    songs s,
            inst_in_song iis,
            instruments i
    where   s.id = iis.id
    and     iis.inst = i.inst;
    Hope this helps.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Nov 2007
    Posts
    7
    PAVB,

    Thank you for the input. This is still not returning the correct data. Notice that in the example above, Back In Black is returned in the query. I have a guitar, but not any drums. Therefore, this should have been excluded from the result set. This is very close though. I was able to get similar results with a query as well, but simply cannot figure out how to get the exact result set that I need.

    Any other ideas? Thanks again for any input.

    Michael

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Here is an idea.

    You cannot change your specifications and blame us for it.

    Query is returning exactly what you asked for...

    Originally Posted by Michael M.
    I want ALL songs that have instruments that are in my collection - even if the song only has one instrument and I have 50 - if that one is in the 50 then I want the song to show up.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Nov 2007
    Posts
    7
    PAVB,

    I didn't change my specification! Per my original post:

    "Forget about the accuracy of the actual example. What I want is to find
    all songs that have instruments that I own. In the example above, the
    query would include Paradise City since I have a guitar and a keyboard,
    but would omit Back in Black since I have a guitar, but don't have a
    drum set."

    I apologize for any confusion. Do you have any other ideas that may help me solve my query?

    Thanks,

    Michael

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Michael...

    I solved what you asked for.

    Originally Posted by Michael M.
    I want ALL songs that have instruments that are in my collection - even if the song only has one instrument and I have 50 - if that one is in the 50 then I want the song to show up.


    You wasted my time and I do not appreciate that. I'll let somebody else continue helping you.

    Cheers!
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Nov 2007
    Posts
    7
    PAVB...you did not solve what I asked for. Even if you read the quote you have put here twice, you will see that it is not solved. "Even if the song only has one instrument and I have 50 - if that one is in the 50 then I want the song to show up". That doesn't say to show songs that have instruments that I don't have. It says that as long as I have ALL instruments from the song then display it - even if the song only has one and I have 50. I further clarified this with the ORIGINAL post which was so clear it could not be left for misinterpretation.

    I don't appreciate you telling me that I wasted your time. I sincerely appreciate the help you supplied, but I don't appreciate the negative tone that you have now taken. I can only assume that this problem is too difficult to solve. I realize it isn't an easy solution and is the reason I came to this board in the first place.

    Hopefully someone will be able to help me. This is beyond my skill set.

    Michael

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    I see... Je suis vraiment désolée.

    Don't worry, you won't have to deal with me anymore, I'll stay as far as I can from your postings
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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


Click Here to Expand Forum to Full Width