Left join

[jas2803]

Try this again, got logged out.....
I have two tables, Employees and Weights
I need a query that shows "ALL" employees in a room, and their first weight if one is available.

SELECT Weight.Weight, Employee.ID ,Employee.study from Employee left join Weight on Employee.ID = Weight.ID Where Employee.Locati on = 'Room 7' and Weight.Date > '10/1/08' Order by study

When I run this I only get employees with weights and if there are more than one weight I get each one displaying their id multiple times....

I need to limit to 1 weight or less but show each employee id
Any ideas?

[davidson1]

Jas..i think u cant do that with left join..but try the following...



SELECT Employee.ID ,max(Weight.Wei ght),Employee.s tudy from Employee,Weight where Employee.ID = Weight.ID and Employee.Locati on = 'Room 7' and Weight.Date > '10/1/08' group by Employee.ID


if u have any problem..reply me....

[amitpatel66]

Are you looking at displaying one weight per employee and also those employees who does not have any weight?

[jas2803]

Yes

I need all of the employee id's
thanks

[jas2803]

Closer

SELECT Weight.Weight, Employee.ID ,Employee.study from Employee left join Weight on Employee.ID = Weight.ID and Weight.Date > '10/1/08'
Where Employee.Locati on = 'Room 7' and Order by study

This gets me closer but I get multiple weights.

[jas2803]

Got it (I think)

SELECT Employee.ID, Employee.study, MAX(monthly.wei ght) AS wt1
FROM Employee LEFT OUTER JOIN
(SELECT ID, weight
FROM Weight
WHERE Weight.[Date] > '11/1/08') monthly ON Employee.ID = monthly.ID
WHERE (Employee.Locat ion = 'Room 7')
GROUP BY Employee.ID, Employee.study
ORDER BY Employee.study


But really get tired of this site logging me automatically.. .

[suryabeniwal]

try this ...........
SELECT Employee.ID, Employee.study, MAX(monthly.wei ght) AS wt1
FROM Employee LEFT OUTER JOIN
(SELECT ID, weight
FROM Weight
WHERE Weight.[Date] > '11/1/08') monthly ON Employee.ID = monthly.ID
WHERE (Employee.Locat ion = 'Room 7')
GROUP BY Employee.ID, Employee.study
ORDER BY Employee.study