July 11th, 2003, 01:32 PM
|
|
Junior Member
|
|
Join Date: Jun 2003
Posts: 6
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
Trying to figure out a left outer join - MSSQL 7
I am attempting to use a left outer join but am having a problem joining on the correct records.
Here is my query:
Code:
SELECT workorders.woDateTime, workorders.woPickedUpDate, machines.machID, machines.machType
FROM machines LEFT OUTER JOIN
workorders ON machines.machID = workorders.woMachID
WHERE (workorders.woCustID = 993) AND (workorders.woMachID <> 0) AND (workorders.woPickedUpDate IS NULL)
ORDER BY machines.machID
In the database, there are multiple rows in the workorders tables corresponding to each row in the machine table. The machine table has a primary key machID and the workorder table has a foreign key woMachID that corresponds to machID.
What I am trying to do is get the workorder row that has the maximum woDateTime for that machID and also get the value of woPickedUpDate for the row that woDateTime is corresponding to. I can't do an inner join because sometimes it is possible to not have a row in the workorder table for that machID. If this happens then no record would be returned at all.
I seem to be confused on how to do this. If any more explaination is needed please let me know.
Thanks for your time.
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
