Need SQL for Joined Tables

[GGerard]

Hello

I have the following tables joined on a one (Field4) to many (Field3)
relationship

Table1
Field1 / Field2 / Field3
1 Bob 2
2 Fred 4
3 Paul 2
4 John 3
(AutoNum)

Table2
Field4 / Field5
1 Doctor
2 Dentist
3 Lawyer
4 Teacher
(AutoNum)

The following SQL will output :

SELECT Table1.Field1, Table1.Field2, Table1.Field3, Table2.Field4,
Table2.Field5
FROM Table2 INNER JOIN Table1 ON Table2.Field4 = Table1.Field3;

Field1 / Field 2 / Field3 / Field4 /
Field5
1 Bob 2 2
Dentist
3 Paul 2 2
Dentist
2 Fred 4 4
Teacher
4 John 3 3
Lawyer

How can I write an SQL so that only one record with Dentist (in Field5) is
output
with either Bob or Paul but not both?
Example:

Field1 / Field 2 / Field3 / Field4 /
Field5
1 Bob 2 2
Dentist
2 Fred 4 4
Teacher
4 John 3 3
Lawyer
I am using Access 2000

Thanks
G Gerard

[Squirrel]

Hi,
This works, it's a bit long, so maybe someone else will offer a better
solution...

Use 4 separate queries, one for each profession, retrieve the "TOP 1" row
from each and then UNION the results together.

In Access Help search for this: "Create an SQL-specific query"
It describes how to create a union query in the query window.

tblProfession
ProfessionId
ProfessionName

tblPerson
PersonID
ProfessionID
PersonName

SQL for Query1
SELECT TOP 1 tblPerson.PersonName, tblProfession.ProfessionName
FROM tblProfession INNER JOIN tblPerson ON tblProfession.ProfessionId =
tblPerson.ProfessionID
WHERE (((tblProfession.ProfessionName)="Doctor"));

SQL for Query2
SELECT TOP 1 tblPerson.PersonName, tblProfession.ProfessionName
FROM tblProfession INNER JOIN tblPerson ON tblProfession.ProfessionId =
tblPerson.ProfessionID
WHERE (((tblProfession.ProfessionName)="Dentist"));

(Queries 3 and 4 same style)

SQL for Query5
SELECT PersonName, ProfessionName FROM Query1
union
SELECT PersonName, ProfessionName FROM Query2
union
SELECT PersonName, ProfessionName FROM Query3
union
SELECT PersonName, ProfessionName FROM Query4
;

Query5 output PersonName ProfessionName
Bob Dentist
Fred Teacher
John Lawyer
HTH -Linda
"GGerard" <gg*****@nbnet.nb.ca> wrote in message
news:Ls*********************@ursa-nb00s0.nbnet.nb.ca...

Hello

I have the following tables joined on a one (Field4) to many (Field3)
relationship

Table1
Field1 / Field2 / Field3
1 Bob 2
2 Fred 4
3 Paul 2
4 John 3
(AutoNum)

Table2
Field4 / Field5
1 Doctor
2 Dentist
3 Lawyer
4 Teacher
(AutoNum)

The following SQL will output :

SELECT Table1.Field1, Table1.Field2, Table1.Field3, Table2.Field4,
Table2.Field5
FROM Table2 INNER JOIN Table1 ON Table2.Field4 = Table1.Field3;

Field1 / Field 2 / Field3 / Field4 /
Field5
1 Bob 2 2
Dentist
3 Paul 2 2
Dentist
2 Fred 4 4
Teacher
4 John 3 3
Lawyer

How can I write an SQL so that only one record with Dentist (in Field5) is
output
with either Bob or Paul but not both?
Example:

Field1 / Field 2 / Field3 / Field4 /
Field5
1 Bob 2 2
Dentist
2 Fred 4 4
Teacher
4 John 3 3
Lawyer
I am using Access 2000

Thanks
G Gerard

[James Fortune]

"GGerard" <gg*****@nbnet.nb.ca> wrote in message news:<Ls*********************@ursa-nb00s0.nbnet.nb.ca>...

Hello

I have the following tables joined on a one (Field4) to many (Field3)
relationship

Table1
Field1 / Field2 / Field3
1 Bob 2
2 Fred 4
3 Paul 2
4 John 3
(AutoNum)

Table2
Field4 / Field5
1 Doctor
2 Dentist
3 Lawyer
4 Teacher
(AutoNum)

The following SQL will output :

SELECT Table1.Field1, Table1.Field2, Table1.Field3, Table2.Field4,
Table2.Field5
FROM Table2 INNER JOIN Table1 ON Table2.Field4 = Table1.Field3;

Field1 / Field 2 / Field3 / Field4 /
Field5
1 Bob 2 2
Dentist
3 Paul 2 2
Dentist
2 Fred 4 4
Teacher
4 John 3 3
Lawyer

How can I write an SQL so that only one record with Dentist (in Field5) is
output
with either Bob or Paul but not both?
Example:

Field1 / Field 2 / Field3 / Field4 /
Field5
1 Bob 2 2
Dentist
2 Fred 4 4
Teacher
4 John 3 3
Lawyer
I am using Access 2000

Thanks
G Gerard

My post yesterday bounced so this time I'll make it shorter.

SELECT First(Table1.Field1) AS Field1, First(Table1.Field2) AS Field2,
Table1.Field3, Table2.Field4ID, Table2.Field5
FROM Table1 INNER JOIN Table2 ON Table1.Field3 = Table2.Field4ID
GROUP BY Table1.Field3, Table2.Field4ID, Table2.Field5
ORDER BY First(Table1.Field1);

produced exactly the results you wanted when either Field3 or Field4
were indexed. When they weren't it produced a different set of data
that still satisfied the criteria. Without the index, Field4 comes up
in an almost random fashion causing a different record in Table1 to
come up as the first record. Note: I have not tested this query on
anything beyond the sample data you show. Also, Squirrel's proposed
table structure looks nice.

James A. Fortune