9 Replies - 534 Views - Last Post: 17 October 2018 - 11:32 AM

#1 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6795
  • View blog
  • Posts: 28,083
  • Joined: 12-December 12

not matched on null

Posted 16 October 2018 - 05:08 AM

The premise should be straightforward but my brain isn't working today. I want to find rows in one table that do not occur in another table.

The issue that concerns me is that one of the columns can be null and, as I recall, we cannot match for equality on NULL(?).

SELECT DISTINCT WO.*, LEG.TargetPrice FROM @companies_without_publication WO
	LEFT OUTER JOIN tblLegacyRecommendationHistory LEG 
	ON WO.companyid = LEG.CompanyId AND WO.recommendationid = LEG.RecommendationId 
	WHERE WO.targetprice is null or WO.targetprice <> LEG.TargetPrice

Am I heading in the right direction?

There are three columns (companyid, recommendationid and targetprice) to match across. If the targetprice is null in @companies_without_publication I want to discover if the corresponding column in the other table is different from null.

Is This A Good Question/Topic? 0
  • +

Replies To: not matched on null

#2 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7433
  • View blog
  • Posts: 15,409
  • Joined: 16-October 07

Re: not matched on null

Posted 16 October 2018 - 06:35 AM

If you're doing a left join, there is a chance LEG table entry doesn't exits. You'd check if the id is null to know that. So, do you really want a left join?

Maybe:
-- If the targetprice is null in @companies_without_publication I want to discover if the corresponding column in the other table is different from null.
FROM @companies_without_publication WO
    JOIN tblLegacyRecommendationHistory LEG ON WO.companyid = LEG.CompanyId AND WO.recommendationid = LEG.RecommendationId
WHERE WO.targetprice is null and LEG.TargetPrice is not null


Was This Post Helpful? 1
  • +
  • -

#3 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15070
  • View blog
  • Posts: 60,177
  • Joined: 12-June 08

Re: not matched on null

Posted 16 October 2018 - 06:46 AM

The row ids do not exist in the second table, or they could but may have different price or null price?

create table #aFoo (id int, price int)
create table #bFoo (id int, price int)

insert into #aFoo(id, price) values (1, 10)
insert into #aFoo(id, price) values (2, 20)
insert into #aFoo(id, price) values (3, 30)
insert into #aFoo(id, price) values (5, 50)

insert into #bFoo(id, price) values (1, null)
insert into #bFoo(id, price) values (2, 50)
insert into #bFoo(id, price) values (4, 40)

select * from #aFoo
select * from #bFoo

drop table #aFoo
drop table #bFoo


id          price
----------- -----------
1           10
2           20
3           30
5           50


id          price
----------- -----------
1           NULL
2           50
4           40


Boiled down example - except may work.

select *
from #bFoo a
except
select *
from #aFoo b

id          price
----------- -----------
1           NULL
2           50
4           40

Was This Post Helpful? 1
  • +
  • -

#4 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6795
  • View blog
  • Posts: 28,083
  • Joined: 12-December 12

Re: not matched on null

Posted 16 October 2018 - 07:11 AM

Thank you both. I'll have to study these when I get a chance.
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6795
  • View blog
  • Posts: 28,083
  • Joined: 12-December 12

Re: not matched on null

Posted 16 October 2018 - 11:06 AM

Explored the above but I am still struggling with this and it is a little tricky to explain.

I have a table with 3 cols companyid, targetprice (some nulls), recommendationid.
I have another table with more than these 3 cols, and details for a company can occur many times.

I want to know of the rows in the first table, which combination of its 3 fields do not occur in the second table.
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6795
  • View blog
  • Posts: 28,083
  • Joined: 12-December 12

Re: not matched on null

Posted 16 October 2018 - 11:13 AM

I think I've got it ;)

SELECT * FROM @companies_without_publication PUB 
	WHERE NOT EXISTS (SELECT 1 FROM tblLegacyRecommendationHistory HIS 
		WHERE HIS.CompanyId = PUB.companyid AND HIS.RecommendationId = PUB.recommendationid 
			AND ((HIS.TargetPrice is null AND PUB.targetprice is null) OR 
				(HIS.TargetPrice = PUB.targetprice)))

Probably trying to explain it gave me a push.
Was This Post Helpful? 1
  • +
  • -

#7 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7433
  • View blog
  • Posts: 15,409
  • Joined: 16-October 07

Re: not matched on null

Posted 17 October 2018 - 07:52 AM

I'm not sure if this was intended: it looks like you'll get ALL the rows in @companies_without_publication that don't exist at all in tblLegacyRecommendationHistory.
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6795
  • View blog
  • Posts: 28,083
  • Joined: 12-December 12

Re: not matched on null

Posted 17 October 2018 - 09:04 AM

Mmm I'm gonna test it with temp data, it'll be easier to confirm (rather than with the reams of live data I have).
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6795
  • View blog
  • Posts: 28,083
  • Joined: 12-December 12

Re: not matched on null

Posted 17 October 2018 - 09:25 AM

Okay, here's my test.

create table #temp1 (companyid int, price float, recommendationid int)
create table #temp2 (companyid int, price float, recommendationid int, wibble varchar(10))

insert into #temp1 (companyid, price, recommendationid) values
(1, 10, 1),
(1, 20, 2),
(1, null, 3),
(1, 40, 3),
(2, 10, 1),
(2, null, 2)

insert into #temp2 (companyid, price, recommendationid, wibble) values
(1, 10, 10, 'foo'),
(1, 20, 2, 'foo'),
(1, null, 3, 'foo'),
(1, null, 5, 'foo'),
(1, 40, 3, 'foo'),
(1, 40, 2, 'foo'),
(1, 50, 3, 'foo'),
(2, 10, 1, 'foo')

select * from #temp1 t1 
	where not exists (select 1 from #temp2 t2 
		where t2.companyid = T1.companyid and t2.recommendationid = t1.recommendationid 
		and ((t2.price is null and t1.price is null) or (t2.price = t1.price)))

drop table #temp1
drop table #temp2


with results

companyid	price	recommendationid
1	10	1
2	NULL	2


That's looks like what I was after, identifying the rows (combination of 3 fields) that aren't present in the second table.

It's probably/not very efficient ;)
Was This Post Helpful? 0
  • +
  • -

#10 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7433
  • View blog
  • Posts: 15,409
  • Joined: 16-October 07

Re: not matched on null

Posted 17 October 2018 - 11:32 AM

Curiously, you'll get the same results without your where check...
select * 
  from #temp1 t1 
    where not exists (
      select * from #temp2 t2 
        where t2.companyid = T1.companyid and t2.recommendationid = t1.recommendationid 
          )



Perhaps an easier way to see what's going on in where is to label things.
select 
  case 
    when t2.price is null and t1.price is null then 'FILTER: BOTH NULL PRICE'
    when t2.price is null or t1.price is null then 'PASS: ONE NULL'
    when t2.price = t1.price then 'FILTER: MATCH_PRICE'
    else 'PASS: NA'
    end as [Filter Reason],
    t1.companyid, t1.recommendationid, t1.price, t2.price
  from #temp1 t1
    join #temp2 t2 on t2.companyid = T1.companyid and t2.recommendationid = t1.recommendationid
  order by 2,3,1



Results:
Filter Reason           companyid   recommendationid price                  price
----------------------- ----------- ---------------- ---------------------- ----------------------
FILTER: MATCH_PRICE     1           2                20                     20
PASS: NA                1           2                20                     40
FILTER: BOTH NULL PRICE 1           3                NULL                   NULL
FILTER: MATCH_PRICE     1           3                40                     40
PASS: NA                1           3                40                     50
PASS: ONE NULL          1           3                NULL                   50
PASS: ONE NULL          1           3                40                     NULL
PASS: ONE NULL          1           3                NULL                   40
FILTER: MATCH_PRICE     2           1                10                     10

(9 row(s) affected)



You should be able to tweak this until the filter reasons reflect your reasoning and then apply that filter.

This also happens to highlight the non uniqueness of your join.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1