Query for all matching criteria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query for all matching criteria

Author

Topic

jojothepink
Starting Member

4 Posts

Posted - 2008-05-23 : 12:04:35

Hello all,

I need some assistance in forming a query.

Premise:
A user selects multiple values for a field(from a list box) that will be used for a dynamic query.

Example table:
Name | Major
John | English
John | History
Bob | Math
April | English

In this case the field would be Major and the values would be English, History, Math, etc.

Say the user selects both English and History.

Query: Return the name of people who are majors of both(English and History). The result should be John here.

I was able to code the case for any of the selected majors, but not for all selected majors.

Any help is appreciated

.

-MM

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-05-23 : 13:04:21

What did you have for any majors, and how does it not work for all selected?

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-05-23 : 13:11:23

Show us the code that you've got so far.

Terry

jojothepink
Starting Member

4 Posts

Posted - 2008-05-23 : 13:27:24

The VBA formulates the exact SQL query, but I'll try to write up a sample output.

SELECT (fields we are interested in) FROM tbl
WHERE field IN ([User input], [User input], etc.)
GROUP BY (for aggregates)
HAVING (for aggregates)

The code above uses the "IN" condition, so the query produces the 'any matching row' that I mentioned before.

So the results for user selected majors(e.g. English and History):
John | English
John | History
April | English

I can use this result for another purpose, but I am going to need a query that can select the names that have all the matches.

Also, the example table was really just an example. The table I am using concerns a completely different subject matter(not student info).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 13:29:05

How are you passing the selected values as? I think you can pass the selected values as a comma seperated list and if @Majors is parameter which has comma seperated list then you can use it like this

DECLARE @Sql varchar(8000),@Count int

SET @Count=(LEN(@Majors)-LEN(REPLACE(@Majors,',','')))+1
SELECT @Sql='SELECT Name FROM Table WHERE Major IN (''' + REPLACE(@Majors,',',''',''')+ ''') GROUP BY Name HAVING COUNT(DISTINCT Major)=' + @Count 
EXEC(@Sql)

jojothepink
Starting Member

4 Posts

Posted - 2008-05-23 : 13:44:00

Ah, HAVING Count(...)
Sounds good.

I am doing this in Access though, so I'll figure out the code for that.

Thanks.

jojothepink
Starting Member

4 Posts

Posted - 2008-05-23 : 14:36:48

Alright, got it to work.

Access doesn't have Count(DISTINCT ...) so had to work with multiple nested queries.

Thanks to everyone who helped!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources