Query problem / challenge

[DonBerry]

I want to do a query on linked tables in a one to many relationship. I want to return items from the one side that match two or more items on the many side in an "And" form of the "Where" clause, i.e. each item returned must contain all the criteria in the many table. I have been able to do this using multiple queries. Is there an easier way?

 

[sdk]

Hi,

Not really.

You can create a single query with a subquery such as:

Select field1, feild2
from Main
Where Field3 = "My Condition" and
field1 IN (select field1a, count(field1a) as howMany
from manytable
Group By field1a
where count(field1a) > 1;

but this is only a variation of creating queries as datasets & linkin, and probably not as easy to manage.

Cheers

Steve

 

[johnnymac43]

If I understand your requirement correctly, you could try a query in the following form:

Select field1, field2
From Main
Left Join Manytable On linkfield1 = linkfield1
And linkfield2 = linkfield2
Group By field1, field2
Having (((Count(Manytable.linkfield1))>1));

HTH

John

 

[DonBerry]

Thanks for the replys. Here is, hopefully, a better explanation. The one side table is a list of musicians, the many side table is the instruments they each play. I want to search for all musicians who play clarinet AND bass Clarinet And tenor sax for example.

Don

 

[SVO4Turbo]

Assuming that Musicians table can be linked to Instruments table with Musician_Name

Musicians Table:
Joe
Doug

Instruments Table
Joe Drums
Joe Sax
Joe Tenor
Doug Drums
Doug Sax

SELECT Musician.Musician_Name
FROM Musician LEFT JOIN Instruments ON Musician.Musician_Name = Instruments.Musician_Name
WHERE (((Instruments.Instrument) In ('Tenor','Sax','Drums')))
GROUP BY Musician.Musician_Name;

This query returns Joe only.

I think this is what you wanted

 

[DonBerry]

Thanks SVO4Turbo I think this will work for me!

Don

 

[DonBerry]

SVO4Turbo your suggested query returns both Joe and Doug unless I'm doing something wrong. There apparently is nothing that requires them to meet all criteria of the "In" clause just any of the criteria.

Don

 

[SVO4Turbo]

You're right. I actually created this scenario and it didn't work as I had planned. Try adding a count criteria in there. Like this

SELECT Musician.Musician_Name
FROM Musician LEFT JOIN Instruments ON Musician.Musician_Name = Instruments.Musician_Name
WHERE (((Instruments.Instrument) In ('Tenor','Sax','Drums')))
GROUP BY Musician.Musician_Name
HAVING (((Count(Instruments.Instrument))>2));

Here is my Instruments Table:

Musician_Name Instrument
Joe Tenor
Joe Sax
Joe Drums
Doug Drums
Doug Sax
Doug Trombone
Doug Flute

Here is my Musician Table:

Musician_Name
Joe
Doug

Running the above query on the tables listed here returned "Joe" as the result.

Hope that works for you.

 

[DonBerry]

Thanks SVO4Turbo I'll give this a try. It's a lot cleaner than using nested queries as I originally came up with.

Don

 

[johnnymac43]

For a more flexible (and relational) solution you could try this:

Put your data in 3 tables, as follows:

Musician:
Fields: Id, Name
Values: 1, Joe; 2, Doug

Instrument:
Fields: Id, Instrument
Values: a, Drums; b, Sax; c, Tenor

Skill:
Fields: MusicianId, InstrumentId
Values: 1, a; 1, b; 1, c; 2, a; 2, b

To find all Musicians with skill in at least a defined number of Instruments, use the following SQL:

SELECT Musician.Name
FROM (Musician
INNER JOIN Skill ON Musician.Id = Skill.MusicianId)
INNER JOIN Instrument ON Skill.InstrumentId = Instrument.Id
GROUP BY Musician.Name
HAVING (((Count(Musician.Name))>=[Required]));

To find Musicians with skill in all Instruments, enter the value (number of lines in the Instrument table) as the [Required] parameter.

To find Musicians with skill in up to 3 (or whatever) specific Instruments, use the following SQL:

SELECT Musician.Name
FROM (Musician
INNER JOIN Skill ON Musician.Id = Skill.MusicianId)
INNER JOIN Instrument ON Skill.InstrumentId = Instrument.Id
WHERE (((Instrument.Instrument)=[Instrument 1]))
OR (((Instrument.Instrument)=[Instrument 2]))
OR (((Instrument.Instrument)=[Instrument 3]))
GROUP BY Musician.Name
HAVING (((Count(Musician.Name))>=[Required]))
OR (((Count(Musician.Name))>=[Required]))
OR (((Count(Musician.Name))>=[Required]));

Note that this will allow you to find, for example, all Musicians who have skill in any 1, any 2, or all 3 of a list of 3 specific Instruments.

This is endless fun, but now I must go and do some real work.

Cheers,

John

 

[tlbroadbent]

Here is another method. Use the correct columns to join the tables.

SELECT m.Musician_Name
FROM Musician
INNER JOIN
[SELECT MusicianName FROM Instruments
Group By MusicianName
HAVING Count(*)>=2]. As i
ON m.MusicianName=i.MusicianName Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[DonBerry]

I have used a form of SVO4Turbo's last suggestion and now have that portion of my application running. It seems to be working quite well. To clarify, I needed to select on the ability to play a specific group of instruments not just a specific number. Thanks to all who replied.

Don