Implementing INTERSECT (INNER JOIN) in MS ACCESS

Question

I've been looking at how to convert what I have as UNION to INTERSECT, but there is no INTERSECT keyword in MS Access.

I've been very confused as to how the following code would look like using inner join if I wanted the intersection of these two select statements instead of the Union of them.

I know I can achieve what I want if I SELECT DISTINCT and then use inner join but the ON keyword really confuses me.

I just started a week ago with SQL so if you see anything else wrong with the code I'll accept all constructive criticism :)

SELECT *
FROM (SELECT [Catt].[Name]
FROM [Catt]
WHERE ((([Catt].[Name]) Like "*" & [forms]![Main]![SrchText] & "*") 
AND [forms]![Main]!   [SrchText] IS NOT Null AND [forms]![Main]![SrchText]<>''  )

UNION

SELECT [Catt].[Name]
FROM [Catt]
WHERE  ((([Catt].[Category]) Like [forms]![Main]![Combo7].Value) AND (([forms]![Main]!    [SrchText] IS Null) OR ([forms]![Main]![SrchText]=''))))  AS Query
ORDER BY [Catt].[Name];

Thank you.

u cant union or intersect if you dont have same number of columns in view — nawfal
Oh, so because I am using [Name] and [Category] in the seconds select, an intersect will not work? The thing is that Union has been working for me so far, just wanted to change to intersection. — BadgerBeaz
Yes I got it. what I meant is you cant union Select * and Select catt.name. I'll give a solution — nawfal
possible duplicate of How can I implement SQL INTERSECT and MINUS operations in MS Access — Joe

nawfal nawfal · Accepted Answer · 2012-05-08T16:32:02

Haven't tested this so definitely you will have a lot of MS Access specific changes to be made. But I ask you not to obfuscate query by unnecessarily quoting each identifier in square blocks:

SELECT     DISTINCT title
FROM       (  
            SELECT [Catt].[Name] as title
            FROM   [Catt]
            WHERE  ([Catt].[Name] Like "*" & [forms]![Main]![SrchText] & "*") AND 
                    [forms]![Main]![SrchText] IS NOT Null AND 
                    [forms]![Main]![SrchText] <>''
           ) AS view1

INNER JOIN 

           (   
            SELECT   [Catt].[Name]
            FROM     [Catt]
            WHERE    [Catt].[Category] Like [forms]![Main]![Combo7].Value AND 
                     ([forms]![Main]![SrchText] IS Null OR 
                      [forms]![Main]![SrchText]='')
           ) AS view2 ON view1.title = view2.Name

ORDER BY   view1.title

But basically something like this does the trick..

Edited as Gordon pointed out

Implementing INTERSECT (INNER JOIN) in MS ACCESS

1 Answers