SQL Join Query

[dimroed]

Hi,

I have an access database set up that I'm accessing through an ASP front end. The data in the database consists mainly of three tables:
Main
===============================
= RecordID (autonumber)
= Name, Phone, etc...
= ReportType (integer)
===============================

Categories
================================
= Index
= Description
= ReportType
================================

Evaluations
================================
= RecordNum (Number, one-to-many with Main.RecordID)
= Pass (Y/N)
= CategoryNum (Number, one-to-many with Category.Index)
================================

Essentially, the categories table contains a bunch of category names. Each record entered into the Main table represents an evaluation record, and by specifying the ReportType, the user picks which Categories to use in the evaluation. Then, the Evaluations table contains n entries for each entry in the Main table, where n is the total number of Categories for this report type.

It's easy to do a query for example to list all records, such as:
"SELECT * FROM Main, Categories, Evaluations WHERE Main.RecordNum = Evaluations.RecordNum AND Evaluations.CategoryNum = Categories.Index"

Now, my problem arises if I want to do a query like this:
Find all records in main where the evaluation for category 1,2 were True (in the Pass field) and category 3 was False. If I try to do something like

"SELECT * FROM Evaluations WHERE (Evaluations.CategoryNum = 1 And Evaluations.PASS) OR (Evaluations.CategoryNum = 2 And Evaluations.Pass) OR (Evaluations.CategoryNum = 3 And Not Evaluations.Pass)"

Then I got all records for which any of these criteria is true, which is not what I want. What I need is some way to make sure that these criteria are met in the Evaluations table and that it groups the evaluations properly into the main records.

To make this a little clearer, Let's say I have this data:

Main
=================================================
Record Name, etc.       ReportType
1      Joe Shmoe .....  1
2      Bill Shmill ...  1
Categories
=================================================
Index  Description       ReportType
1      Category1         1
2      Category2         1
Evaluations
=================================================
RecordNum   Pass   CategoryNum
1             Y        1
1             Y        2
2             Y        1
2             N        2

I want a query that would return only the records where the first category was Passed but the second category was not. This would, in this case, return record 2 (Bill Shmill).

The only way I can think of doing this is to return the whole evaluations table in a recordset and loop through it using the actual ASP code. I believe this will be too slow and cumbersome for my purposes.

Is there a way to do this through pure SQL code?

Thanks,

-dimroed

 

[danvlas]

Select RecordID From Main
Inner Join Evaluations On Main.RecordID = Evaluations.RecordNum
Group By RecordID
Having Count(Evaluations.RecordNum)<> Abs(Sum(Evaluations.Pass))

Basically this will return all RecordID's for which the number of passed evaluations is different from the number of recorded evaluations.

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[dimroed]

OK, but what if I want to perform a more specific search? The query you give will let me find all records where not all categories were passed.

What if I want to do a specific query, that lets the user specify which categories were passed and which ones were not for a given record.

So for example if I have 5 categories, the user could want all records where category 1,2,3 were passed but category 4,5 was failed, or another query where category 1,2 were failed and category 3,4,5 were passed.

Is there a way to perform this operation?

Thanks,
-dimroed