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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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)
Go to Top of Page
   

- Advertisement -