Self Join | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Self Join

I’ve a table TestInvoice UID Buyer Vendor Agent
———– ———- ———- ———-
1 B1 V1 A1
2 B1 V1 NULL
3 B1 NULL A1
4 B1 V2 A1
5 B1 V1 A2
6 B1 V1 A1
7 B2 V1 A1 I want to search for Buyer (Buyer= B1 this case) from table; in such a manner that it return only those rows where Vendors are either Same or Null and Agent is either same or Null UID Buyer Vendor Agent
———– ———- ———- ———-
1 B1 V1 A1
2 B1 V1 NULL
3 B1 NULL A1
6 B1 V1 A1 Please help.
Thanks
Harsh Harsh
Not sure why you call this a self join: you get there with some very basic criteria … SELECT T.* FROM TestInvoice AS T
WHERE (T.Buyer = T.Vendor OR T.Vendor IS NULL)
OR (T.Buyer = T.Agent OR T.Agent IS NULL)

There are multiple records for a Buyer in said table with different Agents and Vendors.
I want to select records
– for a particular Buyer (B1 in this case)
– where the Vendor is either Same for all such records or is NULL
– where the Agent is either Same for all such records or is NULL Hence my result should look like
B1 V1 A1
B1 NULL A1
B1 V1 NULL Sorry if the question was confusing. Thanks in advance
Harsh Harsh
Your requirement doesn’t make sense to me.
While it is easy to identify rows that are NULL, how do you define
"where the Vendor is either Same for all such records …"
"where the Agent is either Same for all such records …"
?
Is it the vendor (agent) that appears most often or the MIN() or what?
Or am I missing something? ———————–
–Frank
http://www.insidesql.de
———————–

Okay, the key phrase is "the same for all such records". Since your requirement is that rows with other values do not exist, why not use NOT EXISTS in the WHERE statement: SELECT T.* FROM TestInvoice AS T
WHERE NOT EXISTS
(SELECT T2.* FROM TestInvoice AS T2
WHERE (T2.Buyer = T.Buyer AND T2.Vendor <> T.Vendor)
OR (T2.Buyer = T.Buyer AND T2.Agent <> T.Agent)) You don’t need to handle NULLs especially, because (T2.Vendor <> T.Vendor) is false when either or both fields are NULL. If we were using EXISTS, that would mean that the row would not be returned, but since we’re using NOT EXISTS there’s no problem. I can’t think of any JOINs that would return any rows in this situation, not even on derived tables.
Uhoh, NULL does not compare to anything, including itself. So I do think you explicitely need to handle NULLs, or? ———————–
–Frank
http://www.insidesql.de
———————–

I’m out of my head today…
Sorry.
set nocount on
use tempdb
create table #t
(
uid int identity
, buyer char(2) null
, vendor char(2)null
, agent char(2)null
)
insert into #t (buyer, vendor, agent) values(‘B1’, ‘V1’, ‘A1’)
insert into #t (buyer, vendor, agent) values(‘B1’, ‘V1’, null)
insert into #t (buyer, vendor, agent) values(‘B1’, null, ‘A1’)
insert into #t (buyer, vendor, agent) values(‘B1’, ‘V2’, ‘A1’)
insert into #t (buyer, vendor, agent) values(‘B1’, ‘V1’, ‘A2’)
insert into #t (buyer, vendor, agent) values(‘B1’, ‘V1’, ‘A1’)
insert into #t (buyer, vendor, agent) values(‘B2’, ‘V1’, ‘A1’) SELECT T.* FROM #t AS T
WHERE NOT EXISTS
(SELECT T2.* FROM #t AS T2
WHERE (T2.Buyer = T.Buyer AND T2.Vendor <> T.Vendor)
OR (T2.Buyer = T.Buyer AND T2.Agent <> T.Agent))
drop table #t
set nocount off uid buyer vendor agent
———– —– —— —–
7 B2 V1 A1
Forgot to post this.
———————–
–Frank
http://www.insidesql.de
———————–

So, fiddling around this, what about this?`
set nocount on
use tempdb
create table #t
(
uid int identity
, buyer varchar(2) null
, vendor varchar(2)null
, agent varchar(2) null
)
insert into #t (buyer, vendor, agent) values(‘B1’, ‘V1’, ‘A1’)
insert into #t (buyer, vendor, agent) values(‘B1’, ‘V1’, null)
insert into #t (buyer, vendor, agent) values(‘B1’, null, ‘A1’)
insert into #t (buyer, vendor, agent) values(‘B1’, ‘V2’, ‘A1’)
insert into #t (buyer, vendor, agent) values(‘B1’, ‘V1’, ‘A2’)
insert into #t (buyer, vendor, agent) values(‘B1’, ‘V1’, ‘A1’)
insert into #t (buyer, vendor, agent) values(‘B2’, ‘V1’, ‘A1’) SELECT buyer, vendor, agent FROM #t AS T
WHERE NOT EXISTS
(SELECT T2.* FROM #t AS T2
WHERE
(T2.Buyer = T.Buyer AND (T.Vendor <> T2.Vendor OR T2.Vendor IS NULL))
AND
(T2.Buyer = T.Buyer AND (T.Agent <> T2.Agent OR T2.Agent IS NULL)))
AND T.Buyer = ‘B1’
GROUP BY buyer, vendor, agent drop table #t
set nocount off buyer vendor agent
—– —— —–
B1 NULL A1
B1 V1 NULL
B1 V1 A1 ———————–
–Frank
http://www.insidesql.de
———————–

Frank, If either T2.Vendor or T.Vendor contains NULL, then the expression (T2.Vendor <> T.Vendor) evaluates to false, like this: DECLARE @t1 VARCHAR(1), @t2 VARCHAR(1)
SET @t1 = ‘1’
SELECT @t1, @t2, CASE WHEN @t1 <> @t2 THEN ‘True’ ELSE ‘False’ END
—- —- —–
1 NULL False The subquery does not return a row where either T2.Vendor or T2.Agent is NULL, provided that neither field contains a non-NULL value that is different. NOT EXISTS means that the subquery must not return any rows at all, and these criteria are satisfied if the two fields are either identical or NULL. Sorry about all the double negative logic, but that’s just how it works. Adriaan
I think our posts have overlapped [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />———————–<br />–Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />———————–<br />
Not only did our posts overlap, our queries were equivalent as well – at least your second one.[<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]
Frank, Oops, no they are not equivalent queries. Test by inserting an additional row with NULL on both Vendor and Agent: insert into #t (buyer, vendor, agent) values(‘B1’, null, null) Now your query returns no rows, and mine still does.

Well, say I did try to answer the question with the given informations. Your last insert with two NULLs wasn’t mentioned in the original question, so I didn’t bother to take this into account.
However, even if your query returns a row, while mine doesn’t, your statement still fails to return the desired result set.
———————–
–Frank
http://www.insidesql.de
———————–

Frank,<br /><br />Not entirely sure what you mean, unless you refer to the Buyer = ‘B1′ criteria that you added. As per Harsh’s original question, NULLs could be ignored for the comparison, and your query does not ignore them.[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]
Correct, since Harsh’s desired resultset should be<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Hence my result should look like<br />B1 V1 A1<br />B1 NULL A1<br />B1 V1 NULL <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />———————–<br />–Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />———————–<br />
Yes, but B1 NULL NULL should also be ignored, and your query does not.[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />][<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />(Let’s not turn this into a smiley competition.)
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Yes, but B1 NULL NULL should also be ignored, and your query does not.[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />][<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />So, where do you see<br />B1 NULL NULL <br />in here<br /><br />UID Buyer Vendor Agent <br />———– ———- ———- ———- <br />1 B1 V1 A1 <br />2 B1 V1 NULL<br />3 B1 NULL A1 <br />4 B1 V2 A1 <br />5 B1 V1 A2 <br />6 B1 V1 A1 <br />7 B2 V1 A1 <br /><br />[<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />(Let’s not turn this into a smiley competition.)<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Why not <img src=’http://www.insidesql.de/images/cool026.gif’ border=’0′ /><br /><br />———————–<br />–Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />———————–<br />
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Yes, but B1 NULL NULL should also be ignored, and your query does not.[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />][<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />So, where do you see<br /><br />B1 NULL NULL in here<br /><br />UID Buyer Vendor Agent <br />———– ———- ———- ———- <br />1 B1 V1 A1 <br />2 B1 V1 NULL<br />3 B1 NULL A1 <br />4 B1 V2 A1 <br />5 B1 V1 A2 <br />6 B1 V1 A1 <br />7 B2 V1 A1 <br /><br />[<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />You’re right, B1 NULL NULL was not in the sample data. I was anticipating an obvious complication that would produce unexpected results.<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />(Let’s not turn this into a smiley competition.)<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Why not <img src=’http://www.insidesql.de/images/cool026.gif’ border=’0′ /><br /><br />———————–<br />–Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />———————–<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />[<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />]
Who knows anyway? [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br />In the end, I really like those discussions with you [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />Honestly!<br /><br /><br />———————–<br />–Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />———————–<br />
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Who knows anyway? [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />The milkman?<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">In the end, I really like those discussions with you [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />Honestly!<br /><br /><br />———————–<br />–Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />———————–<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />We do seem to argue a lot. Usually a sign of – no, let’s not go there.
Hey, I’m already married [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br />Time for lunch break here.<br /><br />———————–<br />–Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />———————–<br />
I would lie if I said that I wasn’t. What about if I don’t say that I’m not?
]]>