[sql] embedded select

[rh71]

simplified example:

select name, date
from table1
where date >= getdate() - 30 //all results in last 30 days
and name not in ('select distinct name from table2 where date >= getdate() - 45') //all results in last 45 days

(of course the above // remarks are not in my actual sql)

Have names and dates in both tables (not the same data but have coinciding names) and basically trying to get it to show all table1 names as long as they don't exist in table2 (in last 45 days). Why am I getting back all results in last 30 days regardless of the embedded select? I ran the embedded select on its own and it is working fine (more results) and it includes names I can see are in table1. So the check against each other is not working.

Is a join really necessary? Some syntax problem?

 

[KLin]

Remove the single quotes from inside the in clause.

 

[rh71]

I thought that would be the right thing to do if the values were integers. I've looked up samples of them and they had it when the values are obviously varchar. Is that not correct?

Anyway, I tried doing it without the single quotes and it just took forever to run - I stopped it after 10 mins. When run separately, table1 query comes back with 380 records while table2 has over 30,000 to compare with. I need it to strip out any of those 380 records which are among the 30k and give me what's left (and it needs to run dynamically from the database everyday). I don't suppose there's a better way to do this just via sql?

 

[KLin]

http://msdn.microsoft.com/en-us/library/ms177682.aspx

You really need to read the link above. I don't think it should take 10 minutes to run that kind of query though. I would try specifying a static date in the subquery just to see if that makes a difference.

EDIT: Could be a performance issue. try adding an index to the name field in table2. How many record are in table2 if you didn't do a select distinct?

 

[KLin]

SELECT T1.Date, T1.Name
FROM Table1 T1 LEFT JOIN
(
SELECT DISTINCT Name From table2 Where Date >= GetDate() - 45
) T2 ON T1.Name = T2.Name
WHERE T1.Date >= GetDate() - 30 And T2.Name Is Null

That's how I would do it via a join. It will show all names from the table1 with dates greater than 30 days and where there's no match to Table2.

 

[ebaycj]

Originally posted by: KLin
SELECT T1.Date, T1.Name
FROM Table1 T1 LEFT JOIN
(
SELECT DISTINCT Name From table2 Where Date >= GetDate() - 45
) T2 ON T1.Name = T2.Name
WHERE T1.Date >= GetDate() - 30 And T2.Name Is Null

That's how I would do it via a join. It will show all names from the table1 with dates greater than 30 days and where there's no match to Table2.

Better:

DECLARE @d30 DATETIME
DECLARE @d45 DATETIME

SELECT @d30 = GETDATE() - 30
SELECT @d45 = GETDATE() - 45

SELECT
t1.Date,
t1.Name

FROM
Table1 AS t1
LEFT JOIN
(
SELECT DISTINCT Name From table2 AS t2_2 Where t2_2.Date >= @d45
) AS t2
ON t1.Name = t2.Name

WHERE (
(T1.Date >= @d30)
And (T2.Name Is Null)
)

 

[rh71]

thanks guys, I'll give these a try as soon as the damned requester figures out exactly what he wants now... and report back on the results.