Matching Null Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Matching Null Column

Author

Topic

oceanboy
Starting Member

44 Posts

Posted - 2007-06-25 : 03:42:19

Hello~

Is it possible to match the value of columns in 2 tables and the column might have nulls. For example

Table1 - this table is already in the database
Attribute1 Attribute2 Attribute3 ... ... ...
1 1 Null
1 2 Null
1 1 1
1 2 1
1 2 2

Table2 - this table is created after a few processes
Attribute1 Attribute2 Attribute3 ... ... ...
1 1 Null
1 2 Null
1 1 1
1 2 1
1 2 2

Now, I would like to do match these 2 tables. I have tried using join eg.

select *
from table1 a inner join table2 b
on a.attribute1 = b.attribute1 and a.attribute2 = b.attribute2 and a.attribute3 = b.attribute3

However, with this join, I only can get the information for
1,1,1
1,2,1
1,2,2

but not
1,1,null
1,2,null

Is there other better way to do this?

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-25 : 03:44:19

[code]SELECT *
FROM table1 a INNER JOIN table2 b
ON a.attribute1 = b.attribute1
AND a.attribute2 = b.attribute2
AND (
a.attribute3 = b.attribute3
OR (a.attribute3 IS NULL AND b.attribute3 IS NULL)
)[/code]

KH
[spoiler]Time is always against us[/spoiler]

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-25 : 03:53:12

select *
from table1 a inner join table2 b
on a.attribute1 = b.attribute1 and
a.attribute2 = b.attribute2 and
coalesce(a.attribute3,0) = coalesce(b.attribute3,0)

--------------------------------------------------
S.Ahamed

oceanboy
Starting Member

44 Posts

Posted - 2007-06-25 : 04:03:46

thanks khtan!

whao~ if that's the case, my sql statement will go super long~ hahaha~ Perhaps my other statement was written in a long winded way.

Can I ask you extra question if you don't mind, let say i have a table as below
attribute1, attribute2, attribute3, price, count
1 1 1 12 5
1 1 1 13 4
1 1 2 10 3
1 1 2 8 3
1 1 2 11 2

I want to pick up the record where the count is unique, with the attributes as group. Therefore:
1,1,1,12,5 is the answer. How should I write it? My answer to this solution is very long, so I would like to hear/see someone else's advise.

Thanks!

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-25 : 04:13:47

How u r telling

1,1,1,12,5 is unique...explain it clearly

--------------------------------------------------
S.Ahamed

oceanboy
Starting Member

44 Posts

Posted - 2007-06-25 : 04:33:56

ah! my mistake, sorry.

When I say unique, it means the price and the count.

attribute1, attribute2, attribute3, price, count
1,1,1,12,5
1,1,1,11,4
1,1,2,10,3
1,1,2,12,3
1,1,2,9,2
1,1,3,15,6
1,1,3,6,4

as above, you have 2 price for 1,1,1. which is $12 and $11 and $12 has 5 count. Which is why it is unique. On the other hand, 1,1,2 has 2 prices with similar count, which is 3 for $10 and $12 respectively, hence not unique. And for 1,1,3, the record I would like to retrieve is the 1,1,3,15,6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources