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
 Filter manager column (help)

Author  Topic 

blc
Starting Member

3 Posts

Posted - 2014-07-08 : 13:01:03
I made a simple fiddle page for my problem see here: [url]http://sqlfiddle.com/#!2/c7aef/2/0[/url]

What I want is that the Manager column should have a NULL value if the Manager value doesn't exist in USERS table (the PersonID). And also if the Manager value is the same as PersonID it should also be NULL.

Build Schema:

CREATE TABLE USERS(PersonID varchar(2), Company varchar(32));
CREATE TABLE COMPANIES(Name varchar(32), Manager varchar(2));

INSERT INTO USERS VALUES('A1', 'CompanyA');
INSERT INTO USERS VALUES('A2', 'CompanyA');
INSERT INTO USERS VALUES('B1', 'CompanyB');
INSERT INTO USERS VALUES('Z1', 'CompanyZ');

INSERT INTO COMPANIES VALUES('CompanyA', 'A2');
INSERT INTO COMPANIES VALUES('CompanyB', 'C5');

My current query:

SELECT
u.PersonID,
c.Name,
c.Manager
FROM
USERS AS u,
COMPANIES AS c
WHERE
u.Company = c.Name

The result should look something like this:

PERSONID NAME MANAGER
A1 CompanyA A2
A2 CompanyA NULL
B1 CompanyB NULL

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-08 : 13:12:51
I don't think your expected result is correct, but if it is then please explain it better.


SELECT
u.PersonID,
c.Name,
CASE WHEN u.PersonID = c.Manager THEN NULL ELSE c.Manager END AS Manager
FROM COMPANIES AS c
LEFT JOIN USERS AS u ON u.Company = c.Name


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

blc
Starting Member

3 Posts

Posted - 2014-07-08 : 13:20:21
My expected result is correct. I want B1 to have NULL in manager because the manager C5 doesn't exist in USERS table.

I ran your query and it was not what I wanted because it will set C5 as manager for B1.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-08 : 13:48:57
Sorry, I get it now.


SELECT
u1.PersonID,
c.Name,
CASE WHEN u1.PersonID = c.Manager OR u2.PersonID IS NULL THEN NULL ELSE c.Manager END AS Manager
FROM COMPANIES AS c
JOIN USERS AS u1 ON u1.Company = c.Name
LEFT JOIN USERS AS u2 ON u2.PersonID = c.Manager


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

blc
Starting Member

3 Posts

Posted - 2014-07-08 : 13:54:47
I looks ugly, but it works!

I would never been able to come up with that. Thank you very much!
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-07-08 : 14:17:32
Alternative (but not much prettier than tkizer's solution):
select u.personid
,c.name
,m.personid as manager
from users as u
inner join companies as c
on c.name=u.company
left outer join users as m
on m.personid=c.manager
and m.personid<>u.personid
Go to Top of Page

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2014-07-08 : 16:43:02
This is much uglier but much faster

;with temp as(
select *, m =(select manager from COMPANIES c where u.Company=c.Name or u.PersonID=c.Manager) from users u)

select personid, company, manager=(case when m=PersonId or m not in(select personid from users) then null else m end)
from temp where m is not null
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-08 : 16:47:57
quote:
Originally posted by namman

This is much uglier but much faster

;with temp as(
select *, m =(select manager from COMPANIES c where u.Company=c.Name or u.PersonID=c.Manager) from users u)

select personid, company, manager=(case when m=PersonId or m not in(select personid from users) then null else m end)
from temp where m is not null



Define "much faster". I would highly doubt it's faster.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2014-07-08 : 16:58:50
Execution Plan says. Look like other versions force the engine to use hash match join.

The thing more serious is that it is faster, but ... incorrect (maybe)
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-08 : 17:01:06
quote:
Originally posted by namman

Execution Plan says. Look like other versions force the engine to use hash match join.



Hash match join doesn't automatically mean it's going to be slower than one that isn't using it. Unless you are testing against tables with more data in it, I don't see how performance can be compared.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -