get distinct values from a table with inner joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

get distinct values from a table with inner joins

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

30421 Posts

Posted - 2007-01-05 : 10:16:59

LEFT JOIN?

Peter Larsson
Helsingborg, 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 following

SELECT		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

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 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[/code]

Peter Larsson
Helsingborg, Sweden

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-01-08 : 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 - 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 Larsson
Helsingborg, Sweden

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-01-08 : 08:57:21

had an error in the query works now thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources