12 Replies - 246 Views - Last Post: 24 October 2013 - 09:59 AM

#1 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 90
  • Joined: 07-October 11

Double select that removes the duplicates in one statement

Posted 23 October 2013 - 05:42 AM

Good day

I have table named employees.

ID        Name        Surname      ManID
Emp11     Jan         Helen        Emp13
Emp12     Koos        Dup          Emp13
Emp13     Liezel      Lubbe        Emp12


Meaning Jan Helen, Koos Dup Managers is Liezel Lubbe and Liezel Manager is Koos Dup.

Now I want the manager Name and Surnames with just one query, I did this and it working fine.

select a.Employee_ID as ID,a.Employee_Name as Name,a.Employee_Surname as Surname,b.Employee_ID as ManagerID,
,b.Employee_Name as ManagerName,b.Employee_Surname as ManagerSurname from dbo.lms_Employee  a, dbo.lms_Employee  b
where a.Employee_Manager = b.Employee_ID


My problem now is that I get this results.

Emp13      Liezel     Lubbe
Emp13      Liezel     Lubbe
Emp12      Koos       Dub


I want to display the results in grid view but I only want one result like :

Emp13      Liezel     Lubbe
Emp12      Koos       Dub


So in my statement I want to remove the duplicates selected.

Hope can assist me.

Is This A Good Question/Topic? 0
  • +

Replies To: Double select that removes the duplicates in one statement

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8379
  • View blog
  • Posts: 31,148
  • Joined: 12-June 08

Re: Double select that removes the duplicates in one statement

Posted 23 October 2013 - 06:57 AM

Use the keyword 'DISTINCT' in your select statement.
Was This Post Helpful? 0
  • +
  • -

#3 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 90
  • Joined: 07-October 11

Re: Double select that removes the duplicates in one statement

Posted 24 October 2013 - 08:00 AM

Good day Modi123_1

Thing is it is a double select statement so 3 or more employees have the same manger and if I put 'distinct' in my select statement it still bring 3 times the manager back because there is 3 employees reporting to him If i can move 'distinct' to a different part of statement like the last part where I select managers it would be wonderful./ Problem is don't know how.

Any Ideas?
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8379
  • View blog
  • Posts: 31,148
  • Joined: 12-June 08

Re: Double select that removes the duplicates in one statement

Posted 24 October 2013 - 08:06 AM

I don't follow. Distinct removes duplicate lines.. that's its job. I am unclear how you were trying to use it.

As for your double select that should be a join.
Was This Post Helpful? 0
  • +
  • -

#5 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 90
  • Joined: 07-October 11

Re: Double select that removes the duplicates in one statement

Posted 24 October 2013 - 08:21 AM

Ok the statement is from one table.

select [b]'Distinct'[/b] a.Employee_ID as ID,a.Employee_Name as Name,a.Employee_Surname as Surname,b.Employee_ID as ManagerID,
,b.Employee_Name as ManagerName,b.Employee_Surname as ManagerSurname from [b]dbo.lms_Employee  a, dbo.lms_Employee  b[/b]
where a.Employee_Manager = b.Employee_ID


I have one table with columns ID, Surname, Name, ManagerID and that is the reason why I used double select. I don't know of anything where you can join table in the same table?

In the above code i use distinct at beginning of statement witch is right? But it is only going to remove duplicates from the fist part of statement with consists of the employees.

The second part however is getting the managers of these employees selected in same table from the ManagerID witch is the Unique Identifier in this case and ManagerID is actialy the ID from the table. So the statement return the values of the managers but there is duplicates and I don't know how to remove them.
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8379
  • View blog
  • Posts: 31,148
  • Joined: 12-June 08

Re: Double select that removes the duplicates in one statement

Posted 24 October 2013 - 08:32 AM

Quote

I have one table with columns ID, Surname, Name, ManagerID and that is the reason why I used double select. I don't know of anything where you can join table in the same table?

Yes.. you can. I do it frequently with employee information and location data.

Quote

In the above code i use distinct at beginning of statement witch is right? But it is only going to remove duplicates from the fist part of statement with consists of the employees.

No.. distinct works across all the selected columns.

Example:

cre ate tab le #foo(lVal int, sVal1 varchar(20), lLevel int, lManager int)

IN SERT INTO #foo(lVal , sVal1 , lLevel, lManager )
VAL UES (1, 'tim', 0, 4)

INS ERT INTO #foo(lVal , sVal1 , lLevel, lManager )
VAL UES (1, 'tim', 0, 4)

INS ERT INTO #foo(lVal , sVal1 , lLevel , lManager)
VAL UES (2, 'alice', 0, 5)


select lVal , sVal1 , lLevel, lManager
from #foo


select distinct lVal , sVal1 , lLevel, lManager
from #foo

dr op tab le #foo



output:
lVal        sVal1                lLevel      lManager
----------- -------------------- ----------- -----------
1           tim                  0           4
1           tim                  0           4
2           alice                0           5

(3 row(s) affected)

lVal        sVal1                lLevel      lManager
----------- -------------------- ----------- -----------
1           tim                  0           4
2           alice                0           5




Now if you extrapolate that out and use a join (like you should be doing).. assuming your keys are unique you shouldn't have duplicates..

cre ate ta ble #foo(lVal int, sVal1 varchar(20), lLevel int, lManager int)


INS E RT INTO #foo(lVal , sVal1 , lLevel, lManager )
VALUES (1, 'tim', 0, 4)
 
INS ERT INTO #foo(lVal , sVal1 , lLevel , lManager)
VAL UES (2, 'alice', 0, 5)

INS ERT INTO #foo(lVal , sVal1 , lLevel , lManager)
VAL UES (3, 'bob', 0, 4)

INS ERT INTO #foo(lVal , sVal1 , lLevel , lManager)
VAL UES (4, 'carl', 1, 9)

INS ERT INTO #foo(lVal , sVal1 , lLevel , lManager)
VAL UES (5, 'eve', 1, 9)

INS ERT INTO #foo(lVal , sVal1 , lLevel , lManager)
VAL UES (6, 'fred', 0, 5)



select lVal , sVal1 , lLevel, lManager
from #foo

select a.lVal , a.sVal1 , a.lLevel, a.lManager, b.sVal1 as managername
from #foo a
join #foo b on a.lmanager = b.lVal


dr op tab le #foo



Output

lVal        sVal1                lLevel      lManager
----------- -------------------- ----------- -----------
1           tim                  0           4
2           alice                0           5
3           bob                  0           4
4           carl                 1           9
5           eve                  1           9
6           fred                 0           5

(6 row(s) affected)

lVal        sVal1                lLevel      lManager    managername
----------- -------------------- ----------- ----------- --------------------
1           tim                  0           4           carl
2           alice                0           5           eve
3           bob                  0           4           carl
6           fred                 0           5           eve

Was This Post Helpful? 0
  • +
  • -

#7 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 90
  • Joined: 07-October 11

Re: Double select that removes the duplicates in one statement

Posted 24 October 2013 - 09:06 AM

Ok cool I see what you are doing one question thou

01	lVal        sVal1                lLevel      lManager
02	----------- -------------------- ----------- -----------
03	1           tim                  0           4
04	2           alice                0           5
05	3           bob                  0           4
06	4           carl                 1           9
07	5           eve                  1           9
08	6           fred                 0           5
09	 
10	(6 row(s) affected)
11	 
12	lVal        sVal1                lLevel      lManager    managername
13	----------- -------------------- ----------- ----------- --------------------
14	1           tim                  0           4           carl
15	2           alice                0           5           eve
16	3           bob                  0           4           carl
17	6           fred                 0           5           eve


On Line 15 and 17 eve comes up 2 times right? I want to select just the managername column and remove duplicates then so that I only have one of each manger even if he/she comes up more then once in the managername column all in one statement.
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8379
  • View blog
  • Posts: 31,148
  • Joined: 12-June 08

Re: Double select that removes the duplicates in one statement

Posted 24 October 2013 - 09:08 AM

Quote

On Line 15 and 17 eve comes up 2 times right?

No.. why would it? Those rows are two different data finger prints.. My output is - literally - what happens when I run those queries with that data.

Quote

I want to select just the managername column and remove duplicates then so that I only have one of each manger even if he/she comes up more then once in the managername column all in one statement.

I do not understand this requirement.
Was This Post Helpful? 0
  • +
  • -

#9 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 90
  • Joined: 07-October 11

Re: Double select that removes the duplicates in one statement

Posted 24 October 2013 - 09:14 AM

Ok sorry let my rephrase that your output witch is 2 different finger prints that is what I get as well. Now I want to display this data in a gridview but only the managername but in my gridview it is going to come up 2times and I want it to display only once.
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8379
  • View blog
  • Posts: 31,148
  • Joined: 12-June 08

Re: Double select that removes the duplicates in one statement

Posted 24 October 2013 - 09:16 AM

Mock somethign up in paint or something.. I don't get it. If you have two different employees (with the same manager) why would you only want to show that alice's boss is eve, but when displaying fred's line of data you omit the fact that eve is his boss?
Was This Post Helpful? 0
  • +
  • -

#11 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 90
  • Joined: 07-October 11

Re: Double select that removes the duplicates in one statement

Posted 24 October 2013 - 09:19 AM

I only want to Identify any managers that is in the company. And send them a email.
Was This Post Helpful? 0
  • +
  • -

#12 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8379
  • View blog
  • Posts: 31,148
  • Joined: 12-June 08

Re: Double select that removes the duplicates in one statement

Posted 24 October 2013 - 09:31 AM

Okay.. then

lop off any need for the regular employee info:

select distinct b.sVal1 as managername
from #foo a
join #foo b on a.lmanager = b.lVal


managername
--------------------
carl
eve


Was This Post Helpful? 1
  • +
  • -

#13 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 90
  • Joined: 07-October 11

Re: Double select that removes the duplicates in one statement

Posted 24 October 2013 - 09:59 AM

Thank you very much appreciate all the help.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1