By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

Left join

P: 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
Share this Question
Share on Google+
6 Replies


100+
P: 144
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
Expert 100+
P: 2,367
Are you looking at displaying one weight per employee and also those employees who does not have any weight?
Nov 25 '08 #3

P: 30
I need all of the employee id's
thanks
Nov 25 '08 #4

P: 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

P: 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

suryabeniwal
P: 3
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

Post your reply

Sign in to post your reply or Sign up for a free account.