November 9, 2018 at 12:04 pm
Hello
hopefully this makes sense. i have 2 tables, a and b, and i am seeing if table a has a certain row from certain b, however the problem is, what i am joining on sometimes has nulls, and i cant rely on the primary key on neither table because they change, due to bad table architecture, so i have to rely on joining on certain columns, and those columns sometimes have nulls, so.... example is below:
table a
ID Num1 code
1 14 null
2 16 9320-s
3 null s-9292
table b
ID Num1 code
1 14 null
2 16 9320-s
3 null s-9292
so i am trying to use query:
select b.*
from a
left join b
on a.num1 = b.num1
and a.code = b.code
where a.id is null
this code hopefully should tell me if the rows doesnt exist in table a, but exist in table b, then i can use an insert statement to insert it... however again, because the nulls are either in column num1 or code, they dont join correctly... any way to handle this situation?
thanks in advance
November 9, 2018 at 12:07 pm
if A is null and B is null, A = B is not true. Don't think that will help much but it is why your code isn't working.
November 9, 2018 at 12:13 pm
"i am seeing if table a has a certain row from certain b"
select *
from a
where exists(select *
from b
where (b.num1 = a.num1
or b.num1 is null and a.num1 is null)
and (b.code = a.code
or b.code is null and a.code is null)
"this code hopefully should tell me if the rows doesnt exist in table a, but exist in table b"select *
from b
where not exists(select *
from a
where (a.num1 = b.num1
or a.num1 is null and b.num1 is null)
and (a.code = b.code
or a.code is null and b.code is null)
November 9, 2018 at 12:13 pm
any info will help thanks Lynn, but what can i do to get around this :(, have you faced something like this? i hope maybe there is something out there. the good thing is, there is other columns i am joining on as well, but can also have nulls... 🙁
November 9, 2018 at 12:23 pm
Siten0308 - Friday, November 9, 2018 12:04 PMHellohopefully this makes sense. i have 2 tables, a and b, and i am seeing if table a has a certain row from certain b, however the problem is, what i am joining on sometimes has nulls, and i cant rely on the primary key on neither table because they change, due to bad table architecture, so i have to rely on joining on certain columns, and those columns sometimes have nulls, so.... example is below:
table a
ID Num1 code
1 14 null
2 16 9320-s
3 null s-9292table b
ID Num1 code
1 14 null
2 16 9320-s
3 null s-9292so i am trying to use query:
select b.*
from a
left join b
on a.num1 = b.num1
and a.code = b.code
where a.id is nullthis code hopefully should tell me if the rows doesnt exist in table a, but exist in table b, then i can use an insert statement to insert it... however again, because the nulls are either in column num1 or code, they dont join correctly... any way to handle this situation?
thanks in advance
SELECT B.*
FROM B
LEFT OUTER JOIN A on A.num1 = B.num1 and A.code = B.code
WHERE
A.id is NULL
This code should help!!
November 9, 2018 at 12:27 pm
sathwik.em91 - Friday, November 9, 2018 12:23 PMSELECT B.*
FROM B
LEFT OUTER JOIN A on A.num1 = B.num1 and A.code = B.code
WHERE
A.id is NULLThis code should help!!
I don't think that helps with his problem where num1 and code can be null.
November 9, 2018 at 12:47 pm
Siten0308 - Friday, November 9, 2018 12:04 PMHellohopefully this makes sense. i have 2 tables, a and b, and i am seeing if table a has a certain row from certain b, however the problem is, what i am joining on sometimes has nulls, and i cant rely on the primary key on neither table because they change, due to bad table architecture, so i have to rely on joining on certain columns, and those columns sometimes have nulls, so.... example is below:
table a
ID Num1 code
1 14 null
2 16 9320-s
3 null s-9292table b
ID Num1 code
1 14 null
2 16 9320-s
3 null s-9292so i am trying to use query:
select b.*
from a
left join b
on a.num1 = b.num1
and a.code = b.code
where a.id is nullthis code hopefully should tell me if the rows doesnt exist in table a, but exist in table b, then i can use an insert statement to insert it... however again, because the nulls are either in column num1 or code, they dont join correctly... any way to handle this situation?
thanks in advance
Try the following (it's a bit convoluted, but it works well with multiple columns that can independently be null.
SELECT *
FROM a
CROSS APPLY
(
SELECT b.*
FROM b -- get all fields from b
CROSS APPLY
(
SELECT a.num1, a.code
INTERSECT
SELECT b.num1, b.code
) i -- only "join" fields
) j
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 9, 2018 at 12:51 pm
thank you all for your replies, i know, why would they have a column null especially when it needs to be joined on... again bad table architecture, but i am sure you all have seen it all as well.
thank you both Jonathan and SSC guru, your tsql worked 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply