SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Filter manager column (help)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

blc
Starting Member

3 Posts

Posted - 07/08/2014 :  13:01:03  Show Profile  Reply with Quote
I made a simple fiddle page for my problem see here: http://sqlfiddle.com/#!2/c7aef/2/0

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

USA
36845 Posts

Posted - 07/08/2014 :  13:12:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/

Edited by - tkizer on 07/08/2014 13:35:57
Go to Top of Page

blc
Starting Member

3 Posts

Posted - 07/08/2014 :  13:20:21  Show Profile  Reply with Quote
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.

Edited by - blc on 07/08/2014 13:34:28
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 07/08/2014 :  13:48:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 07/08/2014 :  13:54:47  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

370 Posts

Posted - 07/08/2014 :  14:17:32  Show Profile  Reply with Quote
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

USA
272 Posts

Posted - 07/08/2014 :  16:43:02  Show Profile  Reply with Quote
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

USA
36845 Posts

Posted - 07/08/2014 :  16:47:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
272 Posts

Posted - 07/08/2014 :  16:58:50  Show Profile  Reply with Quote
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

USA
36845 Posts

Posted - 07/08/2014 :  17:01:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000