March 17, 2008 at 1:58 pm
I have 2 tables.
master & detail
in master i have columns: Client, Name
in detail i have columns: Client, Date, Status
1st query to retrieve detail records with status of not Successful:
select * from detail
where date >= :start_date and date <= :end_date and
status <> 'Successful'
2nd query to retrieve MISSING detail records:
select * from master
where Client not in (select client from detail
where date >= :start_date and date <= :end_date
Is there a way to combine this into a single query?
March 17, 2008 at 2:10 pm
This is what I came up with. Please note, that if there is detail, you don't have a date to do any comparisions.
select
m.Client,
m.Name,
d.Date,
d.Status
from
dbo.mymaster m
left outer join dbo.mydetail d
on (m.client = d.client)
where
(d.Date >= @start_date
and d.Date <= @end_date
and d.Status <> 'Successful')
or d.Client is null
😎
March 17, 2008 at 2:12 pm
roy.tollison (3/17/2008)
I have 2 tables.master & detail
select * from detail
where date >= :start_date and date <= :end_date and
status <> 'Successful'
select * from master
where Client not in (select client from detail
where date >= :start_date and date <= :end_date
Try this...
select * from master m
where not exists ( select client from detail d where
d.client = m.client and date
between :start_date :end_date )
DAB
March 17, 2008 at 2:30 pm
Question (for anyone):
roy.tollison (3/17/2008)
select * from detailwhere date >= :start_date and date <= :end_date and
status <> 'Successful'
What does the colon (":") on these names (":end_date") mean? They generate a syntax error on my server.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 17, 2008 at 2:46 pm
In my query, I assumed that those are variables, so I replaced them with proper variable names even though I left out the declarations for them.
😎
March 17, 2008 at 2:56 pm
sorry i am using the query's from within Delphi. That is how it assigns a variable name within an sql statement.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply