INTERSECT Query

[hangglide]

Sorry if this has already been answered. I searched and couldn't find it.

How do I do an INTERSECT query in Access? It is my understanding that Access does not support the INTERSECT query. Is this right?

The syntax for an INTERSECT query is:

select field1, field2, … field_n
from tables
INTERSECT
select field1, field2, … field_n
from tables;


Example #1

The following is an example of an INTERSECT query:

select supplier_id
from suppliers
INTERSECT
select supplier_id
from orders;


The INTERSECT query allows you to return the results of 2 or more "select" queries. However, it only returns the rows selected by all queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.

Is there a simple way to do that in Access? The only work around I know is to run the two select queries seperate and then run a third to see what they have in common. There has to be a simpler method (I want to code the solution in VBA script for an application I am writing).

Leo

 

[pbaldy]

Try this (off the top of my head, but I think it will work):

SELECT suppliers.supplier_id
FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

 

[hangglide]

Didn't Work

Thanks for the suggestion Paul but that didn't work like I had hoped. The example I provided was just an example of an INTERSECT query. I guess I will have to get specific with my actual problem.

Here it is.

This is the INTERSECT query I want to run.

SELECT Comment, CommentID
FROM qryComment_CommentType
WHERE CommentTypeID = 41
INTERSECT
SELECT Comment, CommentID
FROM qryComment_CommentType
WHERE CommentTypeID = 14

Note: qryComment_CommentType is the result of quering a many-to-many join (e.g. three table including the join table).

Data in qryComment_CommentType looks like this:
qryComment_CommentType (Comment, CommentID, CommentType, CommentTypeID)

Because Access does not support the "INTERSECT query it will not work". (Side note. If I change "INTERSECT" to "UNION" the UNION equivilent query works.)

 

[FoFa]

INTERSECT is handled by JOINs in access
SO:
SELECT CC1.Comment, CC1.CommentID
FROM qryComment_CommentType As CC1
INNER JOIN qryComment_CommentType As CC2 ON
CC2.CommentID = CC1.CommentID
WHERE CC1.CommentTypeID = 41
AND CC2.CommentTypeID = 14

Should get you what you need (I Think).

 

[Pat Hartman]

FoFa's answer should get you where you want to go. Just out of curisoity, what RDBMS are you using? Access (actually Jet) uses a pretty standard variant of SQL and even does some things that other variants don't such as the crosstab.

 

[FoFa]

I know Oracle (where I looked up their version of Intersect) has UNION, UNION ALL, MINUS and INTERSECT. Maybe because Oracle does not support the Inner, Left, Right join syntax. (Is there a name for that type of join?)

 

[hangglide]

Thanks

Thank you for your helpful replies. FoFa's query worked great. I took a database class in college and we learned Oracle there so that is what I was familliar with. The inner and outer joins were new to me. Thanks for your help.

FYI, I am now using the Access RDBMS. The crosstab option is very cool. I use it a lot now.

Thanks again.