Where Not In / Minus?

[jaybee3]

It's been a while since I've had to use SQL and that was Oracle. Basically I have two tables and I want to just display the items from table a that aren't in table b. Below are the methods of how I thought you would do it, but it doesn't bring in the right results. (The fields are all hypothetical, but fullField has the same format as b.details

SELECT 
   a2.fullField
FROM (
   SELECT
      a.f1, a.f2, a.f2, a.f1 & "-" & a.f2 & "-" & a.f3 As fullField
   FROM
      tableA a) AS a2
WHERE
   a2.fullField NOT IN (
   SELECT
      b.details
   FROM
      tableB b)
;

SELECT 
   a2.fullField
FROM (
   SELECT
      a.f1, a.f2, a.f2, a.f1 & "-" & a.f2 & "-" & a.f3 As fullField
   FROM
      tableA a) AS a2
MINUS
SELECT
   b.details
FROM
   tableB b
;

Any help would be much appreciated

 

[xenou]

This looks right but I'm not sure you meant to duplicate f2:

SELECT 
   a2.fullField
FROM (
   SELECT
      a.f1, a.f2, [COLOR="Red"][B]a.f2,[/B][/COLOR] a.f1 & "-" & a.f2 & "-" & a.f3 As fullField
   FROM
      tableA a) AS a2
WHERE
   a2.fullField NOT IN (
   SELECT
      b.details
   FROM
      tableB b)
;

MINUS is not a very commonly supported operation so it's probably not an option here. What kind of database are you querying and how are you running the query? Do you have any sample data to show what you want to accomplish?

 

[xenou]

Note: for the record, the above construction does work tested in an access database (I used one table with records:

TableA (three fields, two rows)
a b c
d e f

TableB (one field, one row)
a-b-c