Exclude query advise

J

John

Hi

I have table1 and table2 who have many to many relation. I have created a
table3 to resolve the single many to many into two one to many relations.

I need to select records from table1 and table2 but only if neither has a
corresponding "combined" record in table3 ie I am looking for something like

WHERE table1.field1 & table2.field2 not in (Select table3.field1 &
table3.field2 from table3),

but the fields are numeric so I can not concatenate them straight. Can I
instead use

WHERE table1.field1 not in (Select table3.field1 from table3) AND
table2.field2 not in (Select table3.field2 from table3)

as equivalent statement? Would that work or am I missing something?

Thanks

Regards
 
D

Douglas J. Steele

I'm not sure I understand why you've got both table1 and table2 in the same
query. I'd think 2 separate queries would be more appropriate.

Create a query that returns those records in table1 that aren't in table3:

FROM table1 WHERE table1.field1 NOT IN (SELECT DISTINCT field1 FROM table3)

Create a second query that returns those records in table2 that aren't in
table3:

FROM table 2 WHERE table2.field2 NOT IN (SELECT DISTINCT field2 FROM table3)

If you really need to report on the details from both tables in a single
query, you can now write one that joins the two queries you created above.
 
J

John

Actually its like this;

Staff work for clients...a staff cold have worked for multiple clients and a
client could have had several staff worked for it. A "worked" table keeps
track of which staff have worked for which clients. A separate table 3 is
for "not preferred" staff for a client consisting of staff id and client id
fields.

I need a list of staff that has worked for clients i.e. join between staff,
worked and clients tables but excluding those that have their staff/client
pair in the "not preferred" table as well. In other words staff who are
suitable to work with a client by virtue of having worked before for it
without being tagged as "not preferred" by the client.

Makes sense?

Thanks

Regards
 
J

John W. Vinson

Hi

I have table1 and table2 who have many to many relation. I have created a
table3 to resolve the single many to many into two one to many relations.

I need to select records from table1 and table2 but only if neither has a
corresponding "combined" record in table3 ie I am looking for something like

WHERE table1.field1 & table2.field2 not in (Select table3.field1 &
table3.field2 from table3),

but the fields are numeric so I can not concatenate them straight. Can I
instead use

WHERE table1.field1 not in (Select table3.field1 from table3) AND
table2.field2 not in (Select table3.field2 from table3)

as equivalent statement? Would that work or am I missing something?

I think you'll need a UNION query to do this, or even two separate queries. If
Table1 and Table3 don't have a common field or set of fields, what exactly do
you want to see? From the classic "class enrollment" example, would you want
to see a list of class names intermingled with a set of student names? What
exactly are these tables, and what are you trying to accomplish?

John W. Vinson [MVP]
 
D

Douglas J. Steele

So aren't you saying, then, that you need to know what entries are in table3
(the intersection between table1 and table2 that indicates which staff
worked for which client) that aren't in table4 (the intersection between
table1 and table2 that indicates the preferred staff for each client)?

Create a query (let's call it query1) that joins table3 and table4 like:

SELECT table3.field1, table3.field2
FROM table3 LEFT JOIN table4
ON table3.field1 = table4.field1
AND table3.field2 = table4.field2
WHERE table4.field1 IS NULL

Use that query to join table1 and table2:

SELECT table1.field1, table1.field2, table2.field1, table2.field2
FROM table1 INNER JOIN (table2 INNER JOIN query1
ON table2.field2 = query1.field2) ON table1.field1 = query1.field1

Or am I still missing something?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top