NOT EXISTS - HELP!

[kristinaloupe]

I am a little new at this.. I am trying to write a statement that will allow me to

select tablea.incident_no, tableb.event_type, tableb.event_date from tablea, table b
where tablea.incident_no = tableb.incident_no
and event_date <= getdate()
and not exists (select * from tableb.event_type where
tablea.incident_no=tableb.incident_no)

tableb contains several events .. I want to see only the incident_no where tableb does not have a "hearing" event greater than today.

Can anyone help me out? Thanks in advance for your time!

 

[gmmastros]

What would the SQL Statement look like for "hearing" events that are scheduled for 'greater than today'?

If you write this SQL Query, I'll show you how to join it to another query to pull ONLY the records you want.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kristinaloupe]

select event_type, event_date from tableb
where event_type = 'HEARING'
and event_date <= getdate()

Thanks so so much!

 

[gmmastros]

Try this....

select tablea.incident_no, 
       tableb.event_type, 
       tableb.event_date 
from   tablea
       Inner Join tableb 
         On tablea.incident_no = tableb.incident_no 
       Left Join (
         select incident_no
         from tableb 
         where event_type = 'HEARING' 
         and event_date <= getdate()
         ) As FutureHearing
         On tableb.Incident_on = FutureHearing.Incident_no
Where  event_date <= getdate()
       And FutureHearing.event_type Is NULL

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kristinaloupe]

George.. thanks for helping me... this is the actual query

select vi_incident_pd_status.incident_id,
tb_event.event_type,
tb_event.event_date
from vi_incident_pd_status
Inner Join tb_event
On vi_incident_pd_status.incident_id = tb_event.incident_id
Left Join (
select incident_id
from tb_event
where tb_event.event_type = 'HEARING'
and event_date <= getdate()
) As FutureHearing
On tb_event.Incident_id = FutureHearing.Incident_id
Where event_date <= getdate()
And FutureHearing.event_type Is NULL

but I get an error saying:
Invalid column name event_type --- but this is a valid column in the tbevent table. ???

Thanks again, Kris

 

[r937]

event_type does not exist in the FutureHearing table because you didn't select it, you only selected incident_id

r937.com | rudy.ca