Brain cramp

Okay, I'm having a brain cramp. This query should not be too
difficult, but I'm having a hard time concentrating.
I have 2 tables (well, a lot more than that, but I don't care about
them right now). A Tech table and a workorder table. I need to produce
a single result with all the tech ids and the workorder ids for a
given day. However, if some tech does not have a workorder, I need to
return the tech id and a null workorder id. I've tried a query like:
select tech.Tech_ID, two.wo_id
from tech
left join TechWO two on two.Tech_ID = tech.Tech_ID
where
(two.WO_Date >= '2/3/2005' or two.wo_date is null) and
(two.WO_Date <= '2/3/2005' or two.wo_date is null)
But this does not work for the techs that do not have a work order.
Suggestions?
-JohnJohn Baima wrote:
> Okay, I'm having a brain cramp. This query should not be too
> difficult, but I'm having a hard time concentrating.
> I have 2 tables (well, a lot more than that, but I don't care about
> them right now). A Tech table and a workorder table. I need to produce
> a single result with all the tech ids and the workorder ids for a
> given day. However, if some tech does not have a workorder, I need to
> return the tech id and a null workorder id. I've tried a query like:
> select tech.Tech_ID, two.wo_id
> from tech
> left join TechWO two on two.Tech_ID = tech.Tech_ID
> where
> (two.WO_Date >= '2/3/2005' or two.wo_date is null) and
> (two.WO_Date <= '2/3/2005' or two.wo_date is null)
>
> But this does not work for the techs that do not have a work order.
> Suggestions?
> -John
You need an outer join if a tech might not have any work orders. Also,
to get a full day (assuming time is stored in the datetime column) you
should say "greater than or equal to today and less then tomorrow).
select tech.Tech_ID, two.wo_id
from tech
left outer join
TechWO two
on two.Tech_ID = tech.Tech_ID
where
(two.WO_Date >= '2/3/2005' and (two.WO_Date < '2/4/2005')
or two.wo_date is null
create table #a (col1 int)
create table #b (col1 int)
insert into #a values (1)
insert into #a values (2)
insert into #a values (3)
insert into #b values (2)
insert into #b values (3)
select a.col1, b.col1
from #a a left outer join #b b
on a.col1 = b.col1
where (b.col1 >= 2 and b.col1 < 3)
or b.col1 is null
col1 col1
-- --
1 NULL
2 2
David Gugick
Imceda Software
www.imceda.com|||Move the date restriction onto the end of you join. Its "nullifying" your
outer join the way you have it.
select tech.Tech_ID, two.wo_id
from tech
left join TechWO two on two.Tech_ID = tech.Tech_ID AND two.WO_Date >=
'2/3/2005' AND two.WO_Date <= '2/3/2005'
Also, you need to be careful when checking date ranges. Depending on how
your date info gets put into the table you may need to worry about the time
part. Midnight is assumed when no time is given. So... the above date rang
e
is really between 2/3/2005 12:00:00 and 2/3/2005 12:00:00 . Is that what
you want? Alternatively you could do this: WO_Date >= '2/3/2005' AND
WO_Date < '2/4/2005'. Look at the DATEADD function in Books Online.
HTH
Paul
Paul
"John Baima" wrote:

> Okay, I'm having a brain cramp. This query should not be too
> difficult, but I'm having a hard time concentrating.
> I have 2 tables (well, a lot more than that, but I don't care about
> them right now). A Tech table and a workorder table. I need to produce
> a single result with all the tech ids and the workorder ids for a
> given day. However, if some tech does not have a workorder, I need to
> return the tech id and a null workorder id. I've tried a query like:
> select tech.Tech_ID, two.wo_id
> from tech
> left join TechWO two on two.Tech_ID = tech.Tech_ID
> where
> (two.WO_Date >= '2/3/2005' or two.wo_date is null) and
> (two.WO_Date <= '2/3/2005' or two.wo_date is null)
>
> But this does not work for the techs that do not have a work order.
> Suggestions?
> -John
>|||Typo:
select tech.Tech_ID, two.wo_id
from tech
left outer join
TechWO two
on two.Tech_ID = tech.Tech_ID
where
(two.WO_Date >= '2/3/2005' and two.WO_Date < '2/4/2005')
or two.wo_date is null
David Gugick
Imceda Software
www.imceda.com|||"Paul" <Paul@.discussions.microsoft.com> wrote:
>Move the date restriction onto the end of you join. Its "nullifying" your
>outer join the way you have it.
Doh! Yes, that is it. Good grief!

>select tech.Tech_ID, two.wo_id
>from tech
>left join TechWO two on two.Tech_ID = tech.Tech_ID AND two.WO_Date >=
>'2/3/2005' AND two.WO_Date <= '2/3/2005'
>Also, you need to be careful when checking date ranges.
Yea, what I gave was a simplification of the real search. Thanks!
-John