problem with intersect operation in a sql query

Question

I wrote the following query, I think it's correct but I have a "missing operator" error.

SELECT * FROM results,Types WHERE results.a=Types.b  
INTERSECT  SELECT * FROM results,Types WHERE results.c=Types.b

Could somebody help me please?

Thanks a lot.

What DB engine and version please? I can't reproduce on SQL Server 2005 SP3 — gbn
– gbn, Commented Jun 2, 2010 at 19:10
I'm pretty sure MSAccess doesn't support INTERSECT. You will need to join on all the columns. Can you indicate the column list returned by * and which of the columns are allowed to be NULL? — Martin Smith
– Martin Smith, Commented Jun 2, 2010 at 19:12
Please share more details such that others can reproduce your problem — Nico Haase
– Nico Haase, Commented Aug 26, 2020 at 13:47

dcp · Accepted Answer · 2010-06-02 23:51:42Z

3

What database are you using? Are you sure that intersect is supported? I tried your query on Oracle (changing the table names to something corresponding to my DB) and it worked ok.

EDIT: Since you confirmed you are using MS-Access, then it's clear that INTERSECT is the problem since it's not supported with MS-Access: http://www.access-programmers.co.uk/forums/archive/index.php/t-86531.html

EDIT2: This is untested, but the basic idea is that you need to find all rows in your first query that exist in your second query. To do that, you will have to compare every column between the 2 queries for a match, as all the columns must match for it to be an "intersected" row.

There may be some syntax issues, but hopefully this gets you started.

SELECT r.col1
     , t.col1
     /* list all other columns here */
  FROM results r
     , types t
 WHERE r.a = t.b
 AND EXISTS (
     SELECT *
       FROM results r2
          , types   t2
      WHERE r2.c = t2.b
        AND NZ(r.col1,0) = NZ(r2.col1,0)
        AND NZ(t.col1,0) = NZ(t2.col1,0)
        /* list other columns here, they all need to match so intersection will work */
 )

edited Jun 2, 2010 at 23:51

answered Jun 2, 2010 at 18:45

dcp

55.7k24 gold badges149 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Shadi Over a year ago

I use MSAccess. I am not sure, I will check now.

Shadi Over a year ago

you're right. it does not support INTERSECTION. So, what should I do if I want to intersect them?

dcp Over a year ago

@Shadi - Yes, it's unfortunate that intersection isn't available. It means you'll have to do things the hard way, see EDIT2 above. I haven't tested it, but I think the idea should hopefully work.

Martin Smith Over a year ago

INTERSECT treats NULLs as equal for comparison purposes so to reproduce that there would need to be a load of IFNULLs or whatever the Access syntax is.

dcp Over a year ago

@Martin Smith - Thank you for your comment. I updated my answer to reflect this. Seems NZ is the equivalent of IFNULL in ACCESS.

gbn · Accepted Answer · 2010-06-02 18:52:05Z

2

It's possible the non-ANSI join is confusing matters

SELECT * FROM results R JOIN Types T ON R.a = T.b  
INTERSECT
SELECT * FROM results R JOIN Types T ON R.c = T.b

answered Jun 2, 2010 at 18:52

gbn

434k84 gold badges602 silver badges690 bronze badges

3 Comments

HLGEM Over a year ago

Even if they aren't confusing matters, implicit joins are a very poor technique and they should be replaced. Of course the query should not be using select * either!

gbn Over a year ago

@HLGEM: one thing at a time :-)

Shadi Over a year ago

It did not work with JOIN as well. SELECT queries work separately, but not together.

Community · Accepted Answer · 2017-05-23 11:47:36Z

2

I would suggest testing each of the SELECT statements separately. Make sure they work by themselves. Then do the INTERSECT.

If you are working with SQL Server, for example, that is not the correct way to refer to two tables. You would need to add a JOIN clause (with an ON specifying the columns to JOIN).

Even if it isn't SQL Server, you need to make sure that the queries each work on their own.

Edit: Someone else has asked here about How can I implement SQL INTERSECT and MINUS operations in MS Access. I see a difference of opinion there, so be sure to test your results to make sure you're getting what you want.

edited May 23, 2017 at 11:47

CommunityBot

11 silver badge

answered Jun 2, 2010 at 18:52

DOK

32.8k8 gold badges64 silver badges93 bronze badges

1 Comment

Shadi Over a year ago

the "SELECT"s work well separately, but they don't work together even with "JOIN".

Sergiu Voicu · Accepted Answer · 2014-07-25 11:49:42Z

0

Use INTERSECT statement only if u want to find common line produce by the individual querys. You're statement:

" SELECT * FROM results,Types WHERE results.a=Types.b
INTERSECT SELECT * FROM results,Types WHERE results.c=Types.b "

don't match the criteria needed for the joint to work."WHERE" clause is creating the problem.

If results.a<>results.c :than u...have nothing to INTERSECT.

answered Jul 25, 2014 at 11:49

Sergiu Voicu

1

Collectives™ on Stack Overflow

problem with intersect operation in a sql query

4 Answers 4

5 Comments

3 Comments

1 Comment

Comments

Your Answer

Linked

Collectives™ on Stack Overflow

problem with intersect operation in a sql query

4 Answers 4

5 Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related