Author |
Topic |
tpiazza55
Posting Yak Master
162 Posts |
Posted - 2007-01-05 : 10:06:14
|
i have a query with some inner joins that return information about an employee i am running into a problem in that i need one employees email base on one inner join and anothers based on another inner join -- is there a way to get both in one query?select emailfrom employeeINNER JOIN technician ON business_location.technician_id = technician.technician_id inner join salesrep on business_location.salesrepid = salesrep.salesrep_idnot sure how to get both of those to employee.emp_id and get the emailtechnician.technician_id = employee.emp_idsalesrep.salesrep_id = employee.emp_id |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-05 : 10:16:59
|
LEFT JOIN?Peter LarssonHelsingborg, Sweden |
|
|
tpiazza55
Posting Yak Master
162 Posts |
Posted - 2007-01-05 : 10:23:45
|
how would that work? |
|
|
tpiazza55
Posting Yak Master
162 Posts |
Posted - 2007-01-05 : 10:37:41
|
the left join returns results but im not sure how i distuncguish the salesrep email and the tech email |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-05 : 18:37:28
|
The basic idea is the followingSELECT e.ID, e.FirstName, e.LastName, MAX(t.eMail) AS [Tech email], MAX(st.eMail) AS [SalesRep email]FROM Employees AS eLEFT JOIN Technicians AS t ON t.id = e.id LEFT JOIN SalesRep AS sr ON sr.id = e.idGROUP BY e.ID, e.FirstName, e.LastNameORDER BY e.LastName, e.FirstName But it is impossible to since you provide no information about the tables.If you have some sample data, please post here. Also post your expected output based on your provided sample data.Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-05 : 18:41:51
|
[code]SELECT DISTINCT e.ID, e.FirstName, e.LastName, CASE WHEN t.ID IS NULL AND sr.ID IS NOT NULL THEN 'SalesRep' WHEN t.ID IS NOT NULL AND sr.ID IS NULL THEN 'Technician' WHEN t.ID IS NOT NULL AND sr.ID IS NOT NULL THEN 'Both Technician and SalesRep' ELSE'<unknown>' END AS [Type of email]FROM Employees AS eLEFT JOIN Technicians AS t ON t.ID = e.IDLEFT JOIN SalesRep AS sr ON sr.ID = e.IDORDER BY e.LastName, e.FirstName[/code]Peter LarssonHelsingborg, Sweden |
|
|
tpiazza55
Posting Yak Master
162 Posts |
Posted - 2007-01-08 : 07:58:21
|
sorry about the double postpeso -- thats is getting me close having trouble getting the value from the case statementCASEWHEN tech.id IS NULL AND salesrep.id IS NOT NULL THEN salesrep.email /*SalesRep*/WHEN tech.id IS NOT NULL AND salesrep.id IS NULL THEN tech.email /*Technician*/WHEN tech.id IS NOT NULL AND salesrep.id IS NOT NULL THEN i need to get them both here tech.email, salesrep.email not working/*Both */ END AS EmailToSendrelevant joins are INNER JOINsales_rep ON business_location.salesrep_id = sales_rep.salesrep_id inner join technician ON business_location.technician_id = technician.technician_id left joinemployee as salesrep ON sales_rep.salesrep_id = salesrep.id left join employee as tech ON technician_id = tech.id |
|
|
tpiazza55
Posting Yak Master
162 Posts |
Posted - 2007-01-08 : 08:38:21
|
ok i got it giving me the values -- how do i get the variables so i can use them,i.e i need the salerep email and the tech email seperate not combined as email |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-08 : 08:39:54
|
Please post some sample data and your expected output based on your provided sample data.Peter LarssonHelsingborg, Sweden |
|
|
tpiazza55
Posting Yak Master
162 Posts |
Posted - 2007-01-08 : 08:57:21
|
had an error in the query works now thanks |
|
|
|