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.

Answer 1

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 */
 )

Answer 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 

Answer 3

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.