468,242 Members | 1,471 Online

# Left join

30
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.Location = '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?
Nov 25 '08 #1
6 1459
davidson1
144 100+
Jas..i think u cant do that with left join..but try the following...

SELECT Employee.ID ,max(Weight.Weight),Employee.study from Employee,Weight where Employee.ID = Weight.ID and Employee.Location = 'Room 7' and Weight.Date > '10/1/08' group by Employee.ID

if u have any problem..reply me....
Nov 25 '08 #2
amitpatel66
2,367 Expert 2GB
Are you looking at displaying one weight per employee and also those employees who does not have any weight?
Nov 25 '08 #3
jas2803
30
I need all of the employee id's
thanks
Nov 25 '08 #4
jas2803
30
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.Location = 'Room 7' and Order by study

This gets me closer but I get multiple weights.
Nov 25 '08 #5
jas2803
30
SELECT Employee.ID, Employee.study, MAX(monthly.weight) 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.Location = 'Room 7')
GROUP BY Employee.ID, Employee.study
ORDER BY Employee.study

But really get tired of this site logging me automatically...
Nov 25 '08 #6
try this ...........
SELECT Employee.ID, Employee.study, MAX(monthly.weight) 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.Location = 'Room 7')
GROUP BY Employee.ID, Employee.study
ORDER BY Employee.study
Nov 27 '08 #7