Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43

    Unanswered: UNION in subqueries

    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?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    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]
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd be pretty surprised if it worked in MS-Access, although it seems likely that it might work in Oracle!

    -PatP

  5. #5
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43
    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)

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Maybe if you try it WITHOUT the parentesis it will work!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •