***///Complex Query Help....!

[jhsurti]

I have this database on Indian Film Songs which I made for personal use. I must explain a bit of databse before posing the question. The songs are sung by singers(may be one, 2 or sometimes even 4-5) So I made a song table, a singers table and a join table like:
Song Table SongID Song etc. (fields)
Singers table SingerID SingerName
SungBy Join Table SongID SingerID

So now for a duet the join table has 2 records with common SongID and different SingerID. If there are 4 singers to a song, there will be for records in the join table.

Now the query I need is that if I wish to get all songs sung by 2 specific singers, I should get those songs only. If I want songs sung by only one singer say s1, I should get only those songs but not other songs in which that singer is one of many other singers.
How do I run this query?
Or does my table need alteration of any sort? I already have entered a 100 songs for testing but I cannot get around this problem.
Any help will be appreciated. It is my personal hobby and so I alone am going to use this database.
Thanks

 

[ByteMyzer]

The following queries will display the data in the table Song Table.

For one singer only (s1):

SELECT DISTINCT [Song Table].*
FROM [Song Table] INNER JOIN
   [SELECT sb1.*
    FROM SungBy AS sb1
    WHERE sb1.SingerID='s1'
    AND NOT EXISTS
        (SELECT sb2.*
         FROM SungBy AS sb2
         WHERE sb2.SongID = sb1.SongID
         AND sb2.SingerID<>'s1';)
    ;]. AS SungBy
ON [Song Table].SongID=SungBy.SongID;

For two singers only (s1 and s2):

SELECT DISTINCT [Song Table].*
FROM [Song Table] INNER JOIN
   [SELECT sb1.*
    FROM SungBy AS sb1
    WHERE EXISTS
        (SELECT sb2.*
         FROM SungBy AS sb2
         WHERE sb2.SongID=sb1.SongID AND sb2.SingerID='s1';)
    AND EXISTS
        (SELECT sb3.*
         FROM SungBy AS sb3
         WHERE sb3.SongID=sb1.SongID AND sb3.SingerID='s2';)
    AND NOT EXISTS
        (SELECT sb4.*
         FROM SungBy AS sb4
         WHERE sb4.SongID=sb1.SongID AND sb4.SingerID Not In ('s1','s2');)
    ;]. AS SungBy
ON [Song Table].SongID=SungBy.SongID;

For three singers only (s1 and s2 and s3):

SELECT DISTINCT [Song Table].*
FROM [Song Table] INNER JOIN
   [SELECT sb1.*
    FROM SungBy AS sb1
    WHERE EXISTS
        (SELECT sb2.*
         FROM SungBy AS sb2
         WHERE sb2.SongID=sb1.SongID AND sb2.SingerID='s1';)
    AND EXISTS
        (SELECT sb3.*
         FROM SungBy AS sb3
         WHERE sb3.SongID=sb1.SongID AND sb3.SingerID='s2';)
    AND EXISTS
        (SELECT sb4.*
         FROM SungBy AS sb4
         WHERE sb4.SongID=sb1.SongID AND sb4.SingerID='s3';)
    AND NOT EXISTS
        (SELECT sb5.*
         FROM SungBy AS sb5
         WHERE sb5.SongID=sb1.SongID AND sb5.SingerID Not In ('s1','s2','s3');)
    ;]. AS SungBy
ON [Song Table].SongID=SungBy.SongID;

See if this helps.

 

[jhsurti]

That does work as intended, ByteMyzer. BUT everytime I wish to run a query I have to first open the Singers Table, search down the list for the singer/s in my mind, look up their corresponding IDs, go to the SQL window and substitute those IDs into the proper places. If I can avoid looking up the ID's and can directly type in the singer's names(which are in the singers table) that would be better. then all i need to do would be to just create a sub and pass the singers' names as variables and construct a SQL statement in the sub and run it from there.
So can you suggest modifying the code you gave me? I think we might have to add some more INNER JOINS perhaps? What would be the max length of the SQL string permitted?
Thanks also for clearing up some SQL words for me like EXISTS (I didn't exactly know how and where to use them).

 

[Rich]

I would suggest you look at some of the sample databases supplied by Microsoft to see how to work with Parameter queries, in your case you only need a combo box on a form to be able to select a singer/song etc.
Multiselect list boxes will allow you to work with multiple selected entries etc.

In fact you don't even need to work with SQL, queries can be built in the query window, and the Wizards will do a great deal of work for you.

You really shouldn't have to go directly into the tables for anything, other than to alter the structure.