Facing problem in selecting distinct values

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

Facing problem in selecting distinct values

Author

Topic

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-12-01 : 02:30:02

Hi,

I want to select distinct id from the squery but it shows all records,

select id from
(
select distinct id, name,age,dept,salary from personaldetails p
left outer join Dept d on p.per_id=d.dep_perid
left outer join salary s on p.per_id=s.sal_id
) detail
order by detail.name, detail.dept

how can I get that.Please help me.
your help is always appreciated.

malay

nr
SQLTeam MVY

12543 Posts

Posted - 2008-12-01 : 02:34:52

select distinct id
from personaldetails p
left outer join Dept d on p.per_id=d.dep_perid
left outer join salary s on p.per_id=s.sal_id

But I'm guessing you want more than this.
Which table does id come from and which other columns do you want?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-12-01 : 02:35:42

hi
just try distinct id in outer select statement..

select distinct id from
(
select distinct id, name,age,dept,salary from personaldetails p
left outer join Dept d on p.per_id=d.dep_perid
left outer join salary s on p.per_id=s.sal_id
) detail
order by detail.name, detail.dept

ok tanx.....

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-12-01 : 08:42:53

I have three table value are
------------------------
table 1: Emp
columns:
a)Emp_Id
b)Emp_Name
c)Emp_Age

table 2: Dept
a)Dep_Id
b)Dep_EmpId
c)Dep_Name

table 3: Salary
a)Sal_Id
b)Sal_EmpId
c)Sal_Amount

Records in tables:
1.Emp table contains only unique records for each employee
2.Dept table contains multiple records of single employee.
3.Sal table has unique records.

Expectation:
I want to retrieve only single empid for all employee who has/has not entry in dept and sal.

condition: there is order by clause

Data:
----
emp_id emp_name emp_age
-----------------------------
1 Rahul 50
2 Ramesh 21

Dept
----
Dep_id Dep_EmpId Dep_name
---------------------------
1 1 xxxxx
2 1 yyyyy
3 1 zzzzzz

the statement I m writing:

select id from
(
select distinct emp_id as id, emp_name as name,emp_age,dep_name as dept,sal_amount from emp e
left outer join Dept d on e.emp_id=d.dep_empid
left outer join salary s on e.emp_id=s.sal_empid
) detail
order by detail.name, detail.dept

The above statement is not returning single id, it return three times
Please help..

malay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 08:50:58

[code]
SELECT *
FROM Employee e
LEFT JOIN Salary s ON s.Sal_EmpId=e.Emp_Id
LEFT JOIN (SELECT d1.*
FROM Dept d1
INNER JOIN (SELECT Dept_EmpId,MAX(Dept_Id) AS Latest
FROM Dept
GROUP BY Dept_EmpId) d2
ON d2.Dept_EmpId=d1.Dept_EmpId
AND d2. Latest=d1.Dept_Id)d
ON d.Dept_EmpId=e.Emp_Id
ORDER BY e.Emp_Name, d.Dep_Name
[/code]

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-12-02 : 01:14:30

Thanks Visakh

malay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 01:17:46

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources