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.ManagerFROM USERS AS u, COMPANIES AS cWHERE u.Company = c.Name The result should look something like this:PERSONID NAME MANAGERA1 CompanyA A2A2 CompanyA NULLB1 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 ManagerFROM COMPANIES AS cLEFT JOIN USERS AS u ON u.Company = c.Name Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
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. |
|
|
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 ManagerFROM COMPANIES AS cJOIN USERS AS u1 ON u1.Company = c.NameLEFT JOIN USERS AS u2 ON u2.PersonID = c.Manager Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
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! |
|
|
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 |
|
|
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 |
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
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) |
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|