SQL Query Help

[Michael M.]

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

[PAVB]

Cool. Show us your best effort and somebody will point you in the right direction, if needed.

[Michael M.]

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

[PAVB]

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...

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.

[Michael M.]

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

[PAVB]

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.

[Michael M.]

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

[PAVB]

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!

[Michael M.]

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

[PAVB]

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