>Hi,
>
>I need to create a SQL Select with a subquery (I think) but I am lost on how to "pass" the value to the subquery.
>
>Here is what I am trying to do:
>
>Table1 has the following fields
>id_field c(15)
>incl_in_sel bit
>Table2 has the following fields
>id_field c(15)
>field2 c(20)
>date_fld t (type DateTime in SQL Server)
>
>Select records from Table1 that have incl_in_sel = 1 (checked) and Table2.date_fld not equal to today and field2 = 'ABC'
>
>For example, he is my attempt:
>
>
>select * from Table1 where incl_in_sel = 1 and Table1.id_field = (select Table2.id_field from Table2 where date_fld <> GetDate() and field2 = 'ABC')
>
>
>But even without trying I don't think the above will work. What am I missing?
If you're looking into a query from table1 where you don't have rows with today's day and field2 = 'ABC' in the second table, then use NOT EXISTS subquery, e.g.
select * from Table1 T1 where not exists (select 1 from Table2 T2 where T2.id_field = T1.id_field
and CAST(T2.date_fld as date) = cast(CURRENT_TIMESTAMP as date)
and T2.field2 = 'ABC')
If it's not broken, fix it until it is.
My Blog