Order By - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Order By

Author

Topic

coinsdrop
Starting Member

4 Posts

Posted - 2007-08-17 : 11:21:44

Hello ,
I am having two tables, where Dept(DeptId,FirstName,LastName,..) and Emp(EmpId,DeptId,RoleId...).
My requirement is I have to show Empl Id,FirstName ,LastName from both the Tables and require the FirstName,Lastname order by.

Query I have written is :

(Select Dept.DeptId,
Case when FirstName is null then '' Else FirstName End + ' ' +
Case when LastName is null then '' Else LastName End as Name
From Dept
where
Dept.DeptId = 1 )

except

(Select Dept.DeptId,
Case when Dept.FirstName is null then '' Else Dept.FirstName End + ' ' +
Case when Dept.LastName is null then '' Else Dept.LastName End as Name
From Emp
right outer Join Dept
on Emp.Empid = Dept.Deptid
where
Dept.DeptId = 1
and
Emp.RoleId = 12
)

Any inputs are greatly appreciated.

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-17 : 12:02:58

IS THIS SOMETHING U WANT, IF YOU DON'T GET CORRECT RESULTS LET ME KNOW

Select Dept.DeptId,
Case when FirstName is null then '' Else FirstName End + ' ' +
Case when LastName is null then '' Else LastName End as Name
From Dept
where
Dept.DeptId = 1

and Dept.DeptId NOT IN

(Select Dept.DeptId,
Case when Dept.FirstName is null then '' Else Dept.FirstName End + ' ' +
Case when Dept.LastName is null then '' Else Dept.LastName End as Name
From Emp
right outer Join Dept
on Emp.Empid = Dept.Deptid
where
Dept.DeptId = 1
and
Emp.RoleId = 12
)
Order By LastName, FirstName

-----------------------------------------------------------------------------------------------
Ashley Rhodes

coinsdrop
Starting Member

4 Posts

Posted - 2007-08-17 : 12:19:16

Ashley,
I tried its not working either.
Select Dept.DeptId,
Case when FirstName is null then '' Else FirstName End + ' ' +
Case when LastName is null then '' Else LastName End as Name
From Dept
where
Dept.DeptId = 1

and Dept.DeptId NOT IN

(Select Dept.DeptId,
Case when Dept.FirstName is null then '' Else Dept.FirstName End + ' ' +
Case when Dept.LastName is null then '' Else Dept.LastName End as Name
From Emp
right outer Join Dept
on Emp.Empid = Dept.Deptid
where
Dept.DeptId = deptId and
Emp.RoleId = 12
)
Order By LastName, FirstName

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-17 : 12:54:53

Is the issue the ORDER BY (try switching LastName and FirstName in the order by)? Are you getting the correct results? Meaning, the rows are correct, just not ordered as you would like?

Are we to assume that your original query is correct in the data it returns, but that you did not know how to get the order by to work? Or is the original query not correct either? Maybe if we can state in words what you are trying to achomplish we can help. Sample data and expexted results are also very helpful.

coinsdrop
Starting Member

4 Posts

Posted - 2007-08-17 : 13:51:20

Lamprey,

The Orginal query I had , getting the Right results,when I want that FirstName And LastName with Order.Can you help me.
Note:Please see the above mention query.

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-17 : 14:04:04

then use

order by firstname, lastname

other than what was your query working without any syntax error.

-----------------------------------------------------------------------------------------------
Ashley Rhodes

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-17 : 14:12:50

Does this work? If not, please let us know what does not work. For that matter, what doesn't work with Ashley's query (other than the FirstName/LastName are reversed in the ORDER BY?

Select 
	DeptId,
	Case when FirstName is null then '' Else FirstName End + ' ' + 
	Case when LastName is null then '' Else LastName End as Name 
From 
	Dept
WHERE
	DeptID = 1
	AND	DeptID NOT IN 
		(
			SELECT 
				DeptID 
			From 
				Emp
			right outer Join 
				Dept
				on Emp.Empid = Dept.Deptid
			where
				Dept.DeptId = 1
				and Emp.RoleId = 12
		)
ORDER BY
	FirstName,
	LastName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources