-2

I have a table (t1) with multiple rows of statuses for different references, one column being a ReferenceID and another column being a StatusID.

t1.ReferenceID - t1.StatusID

A1 - 1
A1 - 2
A1 - 3
A1 - 4
A2 - 1
A2 - 3
A3 - 1
A3 - 3
A4 - 1
A4 - 4
A5 - 2
A5 - 3

I have a second table (t2) which is the list of all available StatusID's

t2.StatusID

1
2
3
4

I need to be able to pull a list of ReferenceID's from t1 where StatusID '1' exists, however it is missing one or more of the other StatusID's in table 2.

i.e. using the above the following referenceID's would be returned:

A2
A3
A4

Triple Clones
  • 1
  • 1
  • Every attempt I have tried has failed so I figured there wasn't much point in showing them. I have tried IN but that appears to be all or nothing, I can get a list of Statuses where ReferenceID doesn't exist but that doesn't help as I dont know what isn't completed. Clearly I'm missing something but I can;t figure out what – Triple Clones Jun 03 '13 at 01:00
  • why is `A5` not return on the list? – John Woo Jun 03 '13 at 01:02
  • Hi JW, I'm only interested in records that have StatusID '1' set. many thanks – Triple Clones Jun 03 '13 at 01:03

1 Answers1

0

Don;t know if this will work on SQLAnywhere.

SELECT  DISTINCT r.ReferenceID
FROM    (SELECT ReferenceID FROM TableName WHERE StatusID = 1 GROUP BY ReferenceID) r
        CROSS JOIN (SELECT StatusID FROM TableName GROUP BY StatusID) d
        LEFT JOIN TableName a
            ON  d.StatusID = a.StatusID AND
                r.ReferenceID = a.ReferenceID
WHERE   a.StatusID IS NULL
ORDER   BY r.ReferenceID
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Many thanks, it does work however I have discovered something else about the data, I need to be able to reference t2 as the complete list of StatusID's as there might not be any ReferenceID's with a complete set of StatusID's. Also other ReferenceID's will exist with StatusID's not relevant for this query. – Triple Clones Jun 03 '13 at 08:16