INNER JOIN and NULL values - 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 2008 Forums

Transact-SQL (2008)

INNER JOIN and NULL values

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2013-11-06 : 13:19:38

Trying to fix a database I inherited, so, created two new tables.
First table, a simple list - tableComm.
Second table, refers to that table with a constraint to tableComm - tableCust
(And no, I don't actually name my tables that way)

Reading from an existing table - tableOLD - and from column CommID.

The select is where I am having a bit of trouble:


SELECT i.pdID, i.custID, c.CommID
FROM tableOLD i
INNER JOIN tableCust c ON c.ID = i.CustID
LEFT JOIN tableCOMM c ON c.ID = i.CommID
WHERE i.CommID IS NOT NULL OR c.ID = 0

And that's where I'm a bit stuck, the WHERE clause.
In the old table, there is not constraint, so some fields are NULL.
If it's NULL, I simply need to give it a value = 0.
But i need all the rows returned.

Suggestions?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-06 : 13:26:42

How did you manage to use same alias for both the tables? Ideally you should get an error for that.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2013-11-06 : 13:33:59

I deleted my own reply. You are right, I got an error while running it.

So, my original question stands.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-06 : 13:39:49

do you mean this?


SELECT i.pdID, i.custID, COALESCE(cm.CommID,cu.CommID)
FROM tableOLD i
INNER JOIN tableCust cu ON cu.ID = i.CustID
LEFT JOIN tableCOMM cm ON cm.ID = i.CommID
WHERE i.CommID IS NOT NULL OR c.ID = 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2013-11-06 : 13:50:52

Not exactly.

What I tried was this:


SELECT i.pdID, i.custID, IsNull(c.CommID, 0) AS CommID
FROM tableOLD i
INNER JOIN tableCust c ON c.ID = i.CustID
LEFT JOIN tableCOMM c ON c.ID = i.CommID

This brings back all the rows I need - all 200000 of them.
And, if in the OLD table, if i.CommID is null, it returns 0.
That is what I needed.

Now, this new problem is, which I am not seeing yet is it's breaking the FK constraint in the new table.

CommID returns values from 0 - 7.
tableCOMM has PK's from 1 - 7......(was about to type 0 - 7)

Arrrrgggggg, I see it now..... DOH!!

IsNull(c.CommID, 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources