Select query

I have 2 tables in an Access DB. An ID may have 1,2 or 3 rows. The g columns may
be have either NULL, an F, or a P. I want to select IDs where none of the rows
for that ID contains an F, and find the name in another table
Another table contains IDs and names
ID g1 g2 g3 g4 sem
-----------------------
AA P 1
AA F P 2
AA P 3
BB P 1
CC P 1
CC P 2
CC 3

ID Name
----------------------
AA Bill
BB Joe
CC Mac

Result set desired:
BB Joe
CC Mac

TIA, Mike

Re: Select query

MikeR wrote:
> I have 2 tables in an Access DB.

You know, it really helps to provide the table names. What's the big
secret? :-)

I will use "t1" and "t2" in my examples.

> An ID may have 1,2 or 3 rows. The g
> columns may be have either NULL, an F, or a P. I want to select IDs
> where none of the rows for that ID contains an F, and find the name
> in another table
> Another table contains IDs and names
> ID g1 g2 g3 g4 sem
> -----------------------
> AA P 1
> AA F P 2
> AA P 3
> BB P 1
> CC P 1
> CC P 2
> CC 3
>
> ID Name
> ----------------------
> AA Bill
> BB Joe
> CC Mac
>
> Result set desired:
> BB Joe
> CC Mac
>
This query is hard because of the less-than-optimum database design.
However, even with the best database design, the query is still pretty
complicated. By using saved queries, we can make it a little less
complicated:

Let's start by converting the data from the first table into a more
optimal format. Create a saved query called FoldedData using this sql (I
will call the first table "t1"):

select ID, 1 As g,g1 as val, sem From t1
union all
select ID, 2,g2, sem From t1
union all
select ID, 3,g3, sem From t1
union all
select ID, 4,g4, sem From t1

When you run this query, you will see how you should have designed this
table. Besides simplifying this task, this design also has the benefit
of simplifying the task of adding g5, g6, etc. to the data: you neither
have to change the table design nor the code that maintains the data.


Now, we can use that query to create another saved query (call it
IDsWithF) that returns only the ID's which have "F" values:

select DISTINCT ID From FoldedData where val = 'F'

Run this query to see that the only ID returned is "AA"

Now use that saved query in an outer join with the second table (call it
"t2") to get your result:

SELECT t2.ID, t2.[Name] From t2 left outer join IDsWithF As q
ON t2.ID = q.ID
WHERE q.ID Is Null

Another way of doing this is with a subquery:

SELECT t2.ID, t2.[Name] From t2
WHERE NOT EXISTS
(select * FROM IDsWithF As q WHERE q.ID=t2.ID)

And yet another way:

SELECT t2.ID, t2.[Name] From t2
WHERE ID NOT IN
(select ID FROM IDsWithF)

The last one is likely to perform the worst. You will want to test these
to ascertain which one provides the best performance.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Select query

Bob Barrows [MVP] wrote:
> You know, it really helps to provide the table names. What's the big
> secret? :-)
I figured since I knew 'em everyone did . Another little fact I discovered I
left out after I ran your solutions - there are IDs in t2 that are not in t1,
and I don't want to return those.
> I will use "t1" and "t2" in my examples.
t1 = STU_G, t2 = STU_Name
> This query is hard because of the less-than-optimum database design.
I'm beginning to see that.
> When you run this query, you will see how you should have designed this
> table. Besides simplifying this task, this design also has the benefit
> of simplifying the task of adding g5, g6, etc. to the data: you neither
> have to change the table design nor the code that maintains the data.
1 & 2 seem pretty equal, so I'll go with 1.
Thanks tons, Bob.

Re: Select query

MikeR wrote:
> Bob Barrows [MVP] wrote:
>> You know, it really helps to provide the table names. What's the big
>> secret? :-)
> I figured since I knew 'em everyone did . Another little fact I
> discovered I left out after I ran your solutions - there are IDs in
> t2 that are not in t1, and I don't want to return those.

Have you handled that or do you require further assistance?
I would probably create a query that returns all the unique id's in t1, then
use an inner join to limit the results of the final query I showed you in my
previous reply. let me know if you need details.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Select query

Bob Barrows [MVP] wrote:
> Have you handled that or do you require further assistance?
> I would probably create a query that returns all the unique id's in t1, then
> use an inner join to limit the results of the final query I showed you in my
> previous reply. let me know if you need details.
Got it. The boot in the right direction was good.