UNION in subqueries

jwab · #1 (**permalink**) 08-26-04, 11:52

I've done this query

Code:

SELECT SO.IMEI
FROM [STOCK OUT] SO
WHERE SO.IMEI NOT IN
 ((SELECT DISTINCT UCS.IMEI
   FROM
    (SELECT DISTINCT CS.IMEI
     FROM [CONNECTED STOCK] CS) 
     UNION
    (SELECT OC.IMEI
     FROM  [OLD CONNECTED STOCK] OC) UCS)
AND DEALER = 'CARMARTHEN'

Just to explain, I need to find all the IMEI's located in CARMARTHEN which are in the STOCK OUT table but are not in the tables CONNECTED STOCK or OLD CONNECTED STOCK.

First of all Access doesnt like it. It says UNION not allowed in subqueries.. which I though was bollocks as I'm sure it is. Perhaps I've done something very wrong I cant see.

Any aid?

Pat Phelan · #2 (**permalink**) 08-26-04, 12:11

I'd suggest using a slightly different approach, something like:

Code:

SELECT SO.IMEI
   FROM [STOCK OUT] AS SO
   WHERE 'CARMARTHEN' = DEALER
      AND NOT EXISTS (SELECT *
         FROM  [CONNECTED STOCK] AS cs
         WHERE  cs.IMEI = so.IMEI)
      AND NOT EXISTS (SELECT * 
         FROM  [OLD CONNECTED STOCK] AS OC
         WHERE oc.IMEI = so.IMEI)

This works on every database engine that supports subqueries that I know of, and can be easily converted to LEFT JOIN syntax for the few engines that don't support sub-queries.

-PatP

LKBrwn_DBA · #3 (**permalink**) 08-26-04, 13:55

Do you think this will work?

Code:

SELECT SO.IMEI
  FROM [STOCK OUT]
 WHERE DEALER = 'CARMARTHEN'
MINUS
SELECT DISTINCT UCS.IMEI
  FROM [CONNECTED STOCK]
MINUS
SELECT OC.IMEI
  FROM [OLD CONNECTED STOCK]

Pat Phelan · #4 (**permalink**) 08-26-04, 14:53

I'd be pretty surprised if it worked in MS-Access, although it seems likely that it might work in Oracle!

-PatP

jwab · #5 (**permalink**) 08-27-04, 07:51

Thanks Pat, works well. Fast to.
I tried yours LKBrwn_DBA but it throws syntax errors, I tried to address them but it still wont be accepted

Code:

SELECT SO.IMEI
  FROM [STOCK OUT] AS SO
 WHERE DEALER = 'CARMARTHEN'
MINUS
(SELECT DISTINCT UCS.IMEI
  FROM [CONNECTED STOCK] AS UCS)
MINUS
(SELECT OC.IMEI
  FROM [OLD CONNECTED STOCK] AS OC)

LKBrwn_DBA · #6 (**permalink**) 08-27-04, 08:53

Maybe if you try it WITHOUT the parentesis it will work!

Internet Services

Web Development

Digital Marketing

Consumer Tech