SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 INNER JOIN and NULL values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Zath
Constraint Violating Yak Guru

USA
282 Posts

Posted - 11/06/2013 :  13:19:38  Show Profile  Visit Zath's Homepage  Reply with Quote
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

India
52309 Posts

Posted - 11/06/2013 :  13:26:42  Show Profile  Reply with Quote
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

USA
282 Posts

Posted - 11/06/2013 :  13:33:59  Show Profile  Visit Zath's Homepage  Reply with Quote
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

India
52309 Posts

Posted - 11/06/2013 :  13:39:49  Show Profile  Reply with Quote
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

USA
282 Posts

Posted - 11/06/2013 :  13:50:52  Show Profile  Visit Zath's Homepage  Reply with Quote
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)

Edited by - Zath on 11/06/2013 13:51:47
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000