Basic Query Question 1

[MooseMan]

I have two tables one with camper names and one with the camps they attend. There is a one to many relationship between the two.

I want a basic query that selects all campers registered in either "DC101" or "DC201" but who are not already registered in "DC102" or "DC202".

It should be quite straight forward but I am not finding myself able to make it work.

Any help would be appreciated.

Thanks in advance.

 

[tlbroadbent]

Without knowing the table structures and relationships, I can only offer a query examples based on assumptions. I assume the camper name or ID is contained in the Camps table. I'll assume you use an ID.

Example: Use Not Exists
Select Camper.ID, Camper.Name
From Camper
Inner Join Camp
On Camper.ID=Camp.CamperID
Where (Camp.CampCode="DC101"
Or Camp.CampCode="DC201&quot
And Not Exists
(Select * From Camp
Where Camp.CamperID=Camper.ID
And (Camp.CampCode="DC102"
Or Camp.CampCode="DC202&quot)

Example: Use Not In
Select Camper.ID, Camper.Name
From Camper
Inner Join Camp
On Camper.ID=Camp.CamperID
Where (Camp.CampCode="DC101"
Or Camp.CampCode="DC201&quot
And Camper.ID Not IN
(Select Camp.CamperID
From Camp
Where Camp.CampCode="DC102"
Or Camp.CampCode="DC202&quot

Example: Use Combination of Inner and Left Joins with sub-query
Select c1.ID, c2.Name
From (Camper c1
Inner Join Camp c2
On c1.ID=c2.CamperID)
Left Join
(Select camp.CamperID
From Camp
Where (Camp.CampCode="DC102"
Or Camp.CampCode="DC202&quot) c3
On c1.ID=c3.CamperID
Where (c2.CampCode="DC101"
Or c2.CampCode="DC201&quot
And c3.CamperID Is Null

It is good to know a variety of techniques and methods. Each type of query has its place. You will find that query performance will vary depending on table structures and sizes, indexing, etc. Terry L. Broadbent - DBA
Computing Links:

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

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

 

[tlbroadbent]

There is a syntax error in the last query example. I forgot the keyword "As".

Example: Use Combination of Inner and Left Joins with sub-query
Select c1.ID, c2.Name
From (Camper As c1
Inner Join Camp As c2
On c1.ID=c2.CamperID)
Left Join
(Select camp.CamperID
From Camp
Where (Camp.CampCode="DC102"
Or Camp.CampCode="DC202&quot) As c3
On c1.ID=c3.CamperID
Where (c2.CampCode="DC101"
Or c2.CampCode="DC201&quot
And c3.CamperID Is Null
Terry L. Broadbent - DBA
Computing Links:

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

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

 

[MooseMan]

Thanks for responding with the examples. I am just getting my head around these select statements.I am going to impose and ask you to be even more specific.

We have two tables tblCampers and tblCampCampers.
There is a one to many relationship between the two based on the CamperID. CamperID being the "primary key" in tblCampers and the "foreign key" in tblCampCampers.

I want to select the name, address, province and postal code from tblCampers where tblCampCampers.CampID is equal to DC101 or DC201, and they are not registered in either DC102 or DC202.

Thanks again for your help

 

[tlbroadbent]

You just need to change the column and table names in my example to match your actual names. The query should do what you want.

Select
c1.name, c1.address,
c1.province, c1.postal code
From (tblCampers As c1
Inner Join tblCampCampers As c2
On c1.CamperID=c2.CamperID)
Left Join
(Select c4.CamperID
From tblCampCampers c4
Where (c4.CampID="DC102"
Or c4.CampID="DC202&quot) As c3
On c1.CamperID=c3.CamperID
Where (c2.CampID="DC101"
Or c2.CampID="DC201&quot
And c3.CamperID Is Null Terry L. Broadbent - DBA
Computing Links:

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

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

 

[MooseMan]

Thank you very much Terry for all your help. Everything worked great. I have one more small question. I have duplicate query results if campers have attended both previous camps.

How do I suppress duplicates. When I added the key word "DISTINCT" I was given an error by Access.

Thanks again for your help and your patience.

Bruce

 

[tlbroadbent]

Make sure you place DISTINCT immediately after the first SELECT in the query. Terry L. Broadbent - DBA
Computing Links:

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

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

 

[MooseMan]

Terry, Orginally I thought that placing Distinct after the first select was the answer, but when I do that I get an error saying there is a syntax error in the from clause. The query works fine apart from adding "distinct"

Any thoughts?

 

[tlbroadbent]

Please post the query with DISTINCT so it can be analyzed. Terry L. Broadbent - DBA
Computing Links:

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

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

 

[MooseMan]

SELECT DISTINCT c1.[Last Name], c1.[First Name], c1.Address, c1.City, c1.[Province/State], c1.[Postal Code ] AS Expr1
FROM (Campers AS c1 LEFT JOIN [Select c4.[Camper ID]
From Camp_Campers c4
Where (c4.[Camp ID]="DC102"
Or c4.[Camp ID]="DC202&quot]. AS c3 ON c1.[Camper ID] = c3.[Camper ID]) INNER JOIN Camp_Campers AS c2 ON c1.[Camper ID] = c2.[Camper ID]
WHERE (((c2.[Camp ID])="DC101" Or (c2.[Camp ID])="DC201&quot AND ((c3.[Camper ID]) Is Null))
ORDER BY c1.[Last Name];

 

[tlbroadbent]

I think you have found an Access bug. Access changes the structure of the query but when you open, modiufy and then attempt to save it, Access complains about the syntax. Change the "[" and "]." around the sub-query to "(" and &quot" repsectively. Note, you'll have to go through this process each time you modify the query.

I also note an extra space in c1.[Postal Code ]. Remove that space and "As Expr1" that follows it.

SELECT DISTINCT
c1.[Last Name],
c1.[First Name],
c1.Address,
c1.City,
c1.[Province/State],
c1.[Postal Code]
FROM (Campers AS c1
LEFT JOIN (Select c4.[Camper ID]
From Camp_Campers c4
Where (c4.[Camp ID]="DC102"
Or c4.[Camp ID]="DC202&quot) AS c3
ON c1.[Camper ID] = c3.[Camper ID])
INNER JOIN Camp_Campers AS c2
ON c1.[Camper ID] = c2.[Camper ID]
WHERE (((c2.[Camp ID])="DC101"
Or (c2.[Camp ID])="DC201&quot
AND ((c3.[Camper ID]) Is Null))
ORDER BY c1.[Last Name]; Terry L. Broadbent - DBA
Computing Links:

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

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

 

[MooseMan]

Terry,

Thank you so very much for all of your help it is very much appreciated.

 

[Aimee88]

This thread asks virtually the same question I am trying to solve. The problem for me is that this doesn't look like any *query* I have done. Is this text for a VBA module? If it is, how do I use it as a query for a report? Could someone explain this at a lower level?

Thanks!

Aimee

 

[tlbroadbent]

Aimee,

What you see is Jet SQL. There is no VBA code. This is a relatively complex query but it is easy to understand if you have a good grasp of the SQL language. I'll try to describe the parts of the query.

1) a sub-query

The sub-query selects the Camper IDs from the Camp_Campers table where the Camp Id is either "DC102" or "DC202".

2) the Left Join

The Left Join is used to JOIN the CAMPERS table to the sub-query by Camper ID. The LEFT JOIN selects all rows from the Campers table and only matching rows from the Camp_Campers table.

3) However, Bruce wants only rows from Campers that don't match the the rows in the sub-query. Thus the WHERE criterion -AND c3.[Camper ID] Is Null- is used to select only non-matching rows.

4) The Campers table is JOINed a second time to the Camp_Campers table by Camper ID using an INNER JOIN.

5) The rows selected are restricted by the WHERE criteria -c2.[Camp ID]="DC101" Or c2.[Camp ID]="DC201".

6) The ALIASES: c3 is required to provide a name for the sub-query. c1, c2 and c4 are used for convenience and readability.

SELECT DISTINCT
c1.[Last Name],
c1.[First Name],
c1.Address,
c1.City,
c1.[Province/State],
c1.[Postal Code]
FROM (Campers AS c1
[LEFT JOIN (Select c4.[Camper ID]
From Camp_Campers c4
Where (c4.[Camp ID]="DC102"
Or c4.[Camp ID]="DC202&quot) AS c3
ON c1.[Camper ID] = c3.[Camper ID])
INNER JOIN Camp_Campers AS c2
ON c1.[Camper ID] = c2.[Camper ID]
WHERE (c2.[Camp ID]="DC101"
Or c2.[Camp ID]="DC201&quot
AND c3.[Camper ID] Is Null
ORDER BY c1.[Last Name];

Let me know if you have additional questions. Terry L. Broadbent - DBA
Computing Links:

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

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

 

[Aimee88]

Thank you. It's starting to make sense. I've learned a lot this week! (I just found this site a week ago. What a find.)

Aimee