CODEWITHSUNDEEP
ms-access
boblewis
boblewis

Reputation: 93

Combining two Access Queries

I three tables. Table1, Table2, Table3. They all have a field called KB. Can I have one query to accomplish: Get all the information from table2, when Table1 KB = Table2 KB But only if table1 KB doesn't exist in Table3. I currently have 2 Queries. One to determine which KBs in Table1 don't exist in Table3. I then run a query against those results showing me all the records in Table2 that have matching KB. Can I do that all in 1 query, or 2 queries the best way?

Upvotes: 1

Views: 37

Answers (1)

forpas
forpas

Reputation: 164069

You can do it with a combination of EXISTS and NOT EXISTS:

SELECT t2.*
FROM table2 AS t2
WHERE EXISTS (SELECT 1 FROM table1 AS t1 WHERE t1.KB = t2.KB)
  AND NOT EXISTS (SELECT 1 FROM table3 AS t3 WHERE t3.KB = t2.KB)

or with an INNER join of table2 to table1 and a LEFT join of table2 to table1 from which you will return only the non matching rows:

SELECT t2.*
FROM (table2 AS t2 INNER JOIN Table1 ON t2.KB = Table1.KB) 
LEFT JOIN table3 ON t2.KB = table3.KB
WHERE table3.KB IS NULL

This may return duplicate rows of table2 if the relationship of table2 and table1 is 1:n, so in this case you can use DISTINCT:

SELECT DISTINCT t2.*
....................

Upvotes: 1

Related Questions