SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 get distinct values from a table with inner joins
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

tpiazza55
Posting Yak Master

162 Posts

Posted - 01/05/2007 :  10:06:14  Show Profile
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
30281 Posts

Posted - 01/05/2007 :  10:16:59  Show Profile  Visit SwePeso's Homepage
LEFT JOIN?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts

Posted - 01/05/2007 :  10:23:45  Show Profile
how would that work?
Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts

Posted - 01/05/2007 :  10:37:41  Show Profile
the left join returns results but im not sure how i distuncguish the salesrep email and the tech email
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 01/05/2007 :  18:37:28  Show Profile  Visit SwePeso's Homepage
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 01/05/2007 :  18:41:51  Show Profile  Visit SwePeso's Homepage
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
Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts

Posted - 01/08/2007 :  07:58:21  Show Profile
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

Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts

Posted - 01/08/2007 :  08:38:21  Show Profile
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 01/08/2007 :  08:39:54  Show Profile  Visit SwePeso's Homepage
Please post some sample data and your expected output based on your provided sample data.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts

Posted - 01/08/2007 :  08:57:21  Show Profile
had an error in the query works now thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000