0

I have 2 tables. I want to select an Id from one table where a row does not exist in another table based on a date... Only I can't seem to find the correct row in the second table

Table 1

adId    info        tableX      tableY
1       blah, blah     Y          N
2       blah, blah     N          Y
3       blah, blah     N          N
4       blah, blah     N          Y
5       blah, blah     N          Y

Table 2

id      start_date  unitId      adId
1       2014-04-01    1          1
2       2014-03-02    2          1
3       2014-04-01    2          2
4       2014-03-01    0          4

The relationship of the unitID in Table2 comes from two tables X and Y (I'm not sure if its relevant but I'll describe it in case it is)

TableX

unitId    adid
1          1
2          1

TableY

unitId    adId
1           5
2           2
3           4

I want to select adId=1, adId=3, adId=5, from Table 1 as in Table 2, none of them have a date in March (NB adId=2 and 5 does not appear in Table 2 at all)

This will find adId= 2 and 5 but not 1. 

When I do SELECT TOP (100) PERCENT Table1.adId
FROM Table1
WHERE (NOT EXISTS
       (SELECT id, start_date
     FROM Table2
         WHERE (Table1.adId = adId) AND (Table2.start_date BETWEEN '2014-03-01'AND '2014-03-05)
    )
       )

How do you add a clause so that its looking for DISTINCT on adId and UnitID?

Is DISTINCT the best way to approach this or is there another way?

Dwair
  • 67
  • 8
  • 1
    I don't understand why you expect to get 1 with such a query: ad 1 has an entry between 1st and 5th of March in your data. I guess I didn't fully understand your requirements (and I'm not quite sure I understand your tables TableX and TableY or the tableX and tableY fields in Table1) – Gorkk Feb 23 '14 at 14:45
  • The query is just where I am up to - and does not work. It finds the rows that are not in table two at all - but I want to build a query that will find table2.id=1 as well (table2.unitID is different) The output I need will be adId=1, adId=3, adId=5 – Dwair Feb 23 '14 at 16:20

3 Answers3

2
  SELECT A.* 
  FROM Table1 A
  WHERE NOT EXISTS 
    ( SELECT 1 
        FROM Table2 B 
       WHERE B.start_date BETWEEN '2014-03-01'AND '2014-03-05' 
         AND B.adId = A.adId )
Chriseyre2000
  • 2,053
  • 1
  • 15
  • 28
  • This is a nice way to condense my query but it wont find adId=1 (It only finds from table 1 that are not in table 2) The bit I'm finding hard is not selecting table2.id=1 (2014-04-01) – Dwair Feb 23 '14 at 16:14
0

Your approach seems over-complex. How about this one:

SELECT adId
FROM Table1
WHERE adId NOT IN
    (SELECT adId
     FROM Table2
     WHERE start_date BETWEEN '2014-03-01' AND '2014-03-05')

As an alternative, if you strongly oppose subqueries in the WHERE condition, you could do:

SELECT Table1.adId
FROM Table1 LEFT JOIN (SELECT adId
     FROM Table2
     WHERE start_date BETWEEN '2014-03-01' AND '2014-03-05') T2
     ON Table1.adId=T2.adId
WHERE T2.adId=NULL
PaF
  • 3,297
  • 1
  • 14
  • 15
  • Never do not use subquries in where condition. – Hett Feb 23 '14 at 14:46
  • Should that be avoid using subquries in a where condition – Chriseyre2000 Feb 23 '14 at 14:50
  • @Hett - This type of blanket statement might be true for some RDBMSs (e.g. MySQL) but it is not true as a general statement for SQL Server. Though actually [the preferred sub query to use would be `NOT EXISTS`](http://stackoverflow.com/questions/173041/not-in-vs-not-exists/11074428#11074428) – Martin Smith Feb 23 '14 at 14:51
0

You can also use the MINUS set operator:

select adId
  from table1
 where start_date not between '2014-03-01' AND '2014-03-05'
minus
select adId
  from table1
 where start_date between '2014-03-01' AND '2014-03-05'
Brian DeMilia
  • 13,103
  • 1
  • 23
  • 33