help on sql query on Adventueworks db

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

General SQL Server Forums

New to SQL Server Programming

help on sql query on Adventueworks db

Author

Topic

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-04-14 : 21:53:42

I've a sql query for adventure works database,What i'm trying to achieve in this goal is to get the employee name, manager name and their states where the manager is from, so i have worked on a query but i find it difficult. How i approached the task by creating temporary table and invoking the sql query by utilizing joins. But instead of creating a temporary i would like to invoke it. How can i go forth any help would be appreciated. here is query

select hre.EmployeeID,
hre.ContactID,
Firstname + LastName as NAME,
ManagerID ,
psp.Name as STATE
into ##T1
from HumanResources.Employee hre
join Person.Contact pc
on
pc.ContactID=hre.ContactID
join HumanResources.EmployeeAddress hrea
on
hrea.EmployeeID=hre.EmployeeID
join Person.Address pa
on
pa.AddressID=hrea.AddressID
join Person.StateProvince psp
on
pa.StateProvinceID=psp.StateProvinceID

select hre.EmployeeID,
hre.ContactID,
Firstname + LastName as NAME,
ManagerID ,
psp.Name as STATE
into ##T2
from HumanResources.Employee hre
join Person.Contact pc
on
pc.ContactID=hre.ContactID
join HumanResources.EmployeeAddress hrea
on
hrea.EmployeeID=hre.EmployeeID
join Person.Address pa
on
pa.AddressID=hrea.AddressID
join Person.StateProvince psp
on
pa.StateProvinceID=psp.StateProvinceID

select t1.NAME as EMPLOYEE,
t1.STATE as [EMPLOYEE STATE],
t2.NAME as MANAGER,
t2.STATE as [MANAGER STATE]
from ##T1 t1

left join ##T2 t2
on t1.ManagerID=t2.EmployeeID
order by t1.EmployeeID

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-15 : 01:01:25

are you asking how to do this without temp tables? with a whole mess of joins and left joins is one way:


select 
	pc1.FirstName + ' ' + pc1.LastName as EmployeeName 
	,psp1.Name as EmployeeState
	,pc2.FirstName + ' ' + pc2.LastName as ManagerName 
	,psp2.Name as ManagerState
from HumanResources.Employee hre1
join Person.Contact pc1 on pc1.ContactID=hre1.ContactID
join HumanResources.EmployeeAddress hrea1 on hrea1.EmployeeID=hre1.EmployeeID
join Person.Address pa1 on pa1.AddressID=hrea1.AddressID
join Person.StateProvince psp1 on pa1.StateProvinceID=psp1.StateProvinceID
left join HumanResources.Employee hre2 on hre1.ManagerID=hre2.EmployeeID
left join Person.Contact pc2 on pc2.ContactID=hre2.ContactID
left join HumanResources.EmployeeAddress hrea2 on hrea2.EmployeeID=hre2.EmployeeID
left join Person.Address pa2 on pa2.AddressID=hrea2.AddressID
left join Person.StateProvince psp2 on pa2.StateProvinceID=psp2.StateProvinceID
order by ManagerName,EmployeeName

www.elsasoft.org

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-04-17 : 15:38:13

Yes. This, is exactly what i was planning to do. Also, on the same topic. I'm also, trying to use co-related subquery for instance, one the question that worked. Below. How can i do the same way in the above query?

Use a correlated subquery to show the titles that have sales. Show title name, title id and quantity for each table?

select *,
qty = (select sum(qty) from sales x where x.title_id = t.title_id)
from titles t
where exists (select * from sales s where s.title_id = t.title_id)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 15:42:46

The two queries in the original post seems to be identical...

Peter Larsson
Helsingborg, Sweden

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-17 : 16:04:06

yea, they are.

OP was populating two different temp tables with exactly the same result set, then left joining the two.

www.elsasoft.org

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-04-17 : 16:07:00

yes. The only difference in my first query, it creates a temporary table and retrieves from the temp tables. Jezemine did using joins which i was indending to do without creating temp table. Following from Jezemine work, i was wondering if it could be done directly into a select statement using co-related sub quer.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 16:10:02

Yes, we understand that. But since the two queries are identical, the LEFT JOIN has no purpose.

Peter Larsson
Helsingborg, Sweden

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-17 : 16:14:35

well, it has a little purpose, because he was joining on different columns in the two temp tables: t1.ManagerID=t2.EmployeeID

not the most efficient thing - he could have used 1 temp table and a self left join.

or no temp tables at all as in my response above.

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources