| Author |
 Topic  |
|
|
tpiazza55
Posting Yak Master
162 Posts |
 Posted - 01/05/2007 : 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 email
from employee
INNER JOIN technician ON business_location.technician_id = technician.technician_id
inner join salesrep on business_location.salesrepid = salesrep.salesrep_id
not sure how to get both of those to employee.emp_id and get the email
technician.technician_id = employee.emp_id
salesrep.salesrep_id = employee.emp_id
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/05/2007 : 10:16:59
|
LEFT JOIN?
Peter Larsson
Helsingborg, Sweden |
 |
|
|
tpiazza55
Posting Yak Master
162 Posts |
Posted - 01/05/2007 : 10:23:45
|
| how would that work? |
|
|
|
tpiazza55
Posting Yak Master
162 Posts |
Posted - 01/05/2007 : 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
Sweden
29138 Posts |
Posted - 01/05/2007 : 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 e
LEFT JOIN Technicians AS t ON t.id = e.id
LEFT JOIN SalesRep AS sr ON sr.id = e.id
GROUP BY e.ID,
e.FirstName,
e.LastName
ORDER 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 Larsson
Helsingborg, Sweden |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/05/2007 : 18:41:51
|
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 e
LEFT JOIN Technicians AS t ON t.ID = e.ID
LEFT JOIN SalesRep AS sr ON sr.ID = e.ID
ORDER BY e.LastName,
e.FirstName
Peter Larsson
Helsingborg, Sweden |
|
|
|
tpiazza55
Posting Yak Master
162 Posts |
Posted - 01/08/2007 : 07:58:21
|
sorry about the double post
peso -- thats is getting me close
having trouble getting the value from the case statement
CASE
WHEN 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 EmailToSend
relevant joins are
INNER JOIN
sales_rep ON business_location.salesrep_id = sales_rep.salesrep_id
inner join
technician ON business_location.technician_id = technician.technician_id
left join
employee 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 - 01/08/2007 : 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
Sweden
29138 Posts |
Posted - 01/08/2007 : 08:39:54
|
Please post some sample data and your expected output based on your provided sample data.
Peter Larsson
Helsingborg, Sweden |
|
|
|
tpiazza55
Posting Yak Master
162 Posts |
Posted - 01/08/2007 : 08:57:21
|
| had an error in the query works now thanks |
|
|
| |
Topic |
|
get distinct values from a table with inner joins
Forum Locked
