Conditional Join Statement

Question

Conditional Join Statement

rbirchtree

SSC Veteran

Points: 206
More actions
March 10, 2015 at 10:16 am

#398158

Hi, it's my first time here.
I'm trying to write a query that will do an full join and create a column with rows that have joined being displayed and the rows that have not being set as 'No'. This is what I have so far....
SELECT dbo.dwlist.DOMID, dbo.dwlist.SPID, dbo.quarter.[Design_End_Customer OEM], dbo.quarter.Design_Name
FROM dbo.dwlist FULL OUTER JOIN
dbo.quarter ON dbo.dwlist.DOMID = dbo.quarter.RecordID
IF
DOMID IS NULL
THEN
UPDATE dwlist
Set DOMID = 'NO'
WHERE DOMID IS NULL ;
ELSE
FULL OUTER JOIN
dbo.quarter ON dbo.dwlist.DOMID = dbo.quarter.RecordID;
END IF
I don't know if I am on the right track, your suggestions are much appreciated.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I'm sorry, but what you posted doesn't make any sense. Can you please provide some sample data and the expected results based on that sample data? You can find out how to post sample data effectively in the fisrt article linked in my signature line.

-- Gianluca Sartori

rbirchtree SSC Veteran Points: 206 More actions · Answer 2

Thanks for the quick reply Gianluca.

I'm trying to write some SQL that will mimic the Excel statement on the "Source" sheet under the "Existing DW". I uploaded the attachment, unfortunately, I cannot upload csv files.

Thanks

Does that make sense now?

The SQL code should do a full join, create a column, if there is no match it should return "No".

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

When you do a left join or a right join or a full outer join or an outer APPLY, unmatched rows return NULL values. Use the ISNULL or CASE expression to replace the NULLS with the word "NO". If a column has an integer, date or numeric datatype, you will have to convert it to varchar first because you can't mix datatypes in a column. See the examples at the bottom.

A couple of words of warning here:

First of all, what you're doing should only be done in a final presentation of the data. It shouldn't be stored in a permanent table in the database. NULLs have a very specific role to play in SQL and replacing them with the word "NO" will only complicate future development. At the very least you may be changing numbers into a character datatype and forcing yourself into doing conversions every time you want to do calculations.

Second, nulls may often appear in in rows regardless of whether or not they have a match on a join. I assume you do NOT want to replace any existing nulls with the word "NO" For this reason, you should use a case expression to test only the join columns rather than doing an ISNULL on each column. More typing but sounder logic. Notice the final example leaves one of the rowInt numbers as a NULL because the rowID (2) was a match, but the existing value was a null.

-- =============================================================================

declare @table1 table (rowID int identity(1,1), rowdata varchar(50), rowInt int)

declare @table2 table (rowID int identity(1,2), rowdata varchar(50), rowInt int)

insert into @table1

values ('Table 1: RowInt will be ten', 10)

,('Table 1: Rowint will be NULL', NULL)

,('Table 1: Rowint will be nineteen', 19)

insert into @table2

values ('Table 2: RowInt will be twenty', 20)

,('Table 2: Rowint will be twenty-five', 25)

,('Table 2: Rowint will be twenty-nine', 29)

-- display contents of tables

--select * from @table1

--select * from @table2

-- see where nulls appear

select *

from @table1 t1

full outer join @table2 t2 on t1.rowID = t2.rowID

-- replace the nulls with the word "NO"

select ISNULL(convert(varchar(5),t1.RowID),'NO') as RowID1, isnull(t1.rowdata,'NO') as RowID1, ISNULL(convert(varchar(5),t1.RowInt),'NO') as RowInt1

,ISNULL(convert(varchar(5),t2.RowID),'NO') as RowID2, isnull(t2.rowdata,'NO') as RowID2, ISNULL(convert(varchar(5),t2.RowInt),'NO') as RowInt2

from @table1 t1

full outer join @table2 t2 on t1.rowID = t2.rowID

-- to be safe, use CASE expressions based on ROWID for all

select CASE WHEN convert(varchar(5),t1.RowID) IS NULL then 'NO' else convert(varchar(5),t1.RowID) end as RowID1

,CASE WHEN convert(varchar(5),t1.RowID) IS NULL then 'NO' else t1.RowData end as RowData1

,CASE WHEN convert(varchar(5),t1.RowID) IS NULL then 'NO' else convert(varchar(5),t1.RowInt) end as RowInt1

,CASE WHEN convert(varchar(5),t2.RowID) IS NULL then 'NO' else convert(varchar(5),t2.RowID) end as RowID2

,CASE WHEN convert(varchar(5),t2.RowID) IS NULL then 'NO' else t2.RowData end as RowData2

,CASE WHEN convert(varchar(5),t2.RowID) IS NULL then 'NO' else convert(varchar(5),t2.RowInt) end as RowInt2

from @table1 t1

full outer join @table2 t2 on t1.rowID = t2.rowID

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

rbirchtree SSC Veteran Points: 206 More actions · Answer 4

Thanks for the tips Celko. You are not the other of the books are you?