April 13, 2011 at 2:14 pm
ichiyo85 (4/11/2011)
Hi experts,I have 2 tables where I want to join by ID1, IF NOT THEN join by ID2.
Would this code work?
select * from table1
left join table2
on coalesce(table1.ID1,table1.ID2) = coalesce(table2.ID1,table2.ID2)
I feel like I am missing something with this code, but I can't catch where. Please advise. Thank you very much for your input!
First, I'd suggest not using * in your query, but you may very well know this and just be presenting it this way for convenience.
Second, I'd suggest keeping fuctions out of your ON statements or your WHERE statements.
Since you don't have NULL values, you can't use IsNull or Coalesce as both return the first Non-NULL value. If you're using "unassigned", that word will be returned.
I'd suggest
CREATE TABLE #Table1(ID1 int, ID2 int)
CREATE TABLE #Table2(ID1 int, ID2 int)
SELECT * -- because I have no idea what other columns you want
FROM
(SELECT CASE ID1
WHEN 'unassigned' THEN ID2
ELSE ID1
END AS Table1ID
FROM #Table1) T1
left join (SELECT CASE ID1
WHEN 'unassigned' THEN ID2
ELSE ID1
END AS Table2ID
FROM #Table2) T2
on T1.Table1ID = T2.Table2ID
Why do you have 2 possible IDs in the table? Or are these not actual IDs but are simply waht you named some columns for the purpose of the example? I'm not really sure why you have the need for this construct. You might want to look at re-designing the table if you can.
Why don't you want NULLs in your table?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 13, 2011 at 8:44 pm
ChrisM@home: Good question! Would you have any suggestions? That's actually one that I don't know what to do...
April 13, 2011 at 8:56 pm
Stefan Krzywicki: Thank you for your suggestions. I have 2 IDs because I need them both to connect to other tables. Also they are both important in our business.
Also, I don't want NULLs because our boss likes the word "Unassigned" better than "NULL." I am sorry if this is a lame reason.
April 13, 2011 at 9:08 pm
mthurber (4/13/2011)
Let me get this straight - if ID1 is not 'Unassigned' then you want to use it for the join, but if ID1 is 'Unassigned,' then you want to use ID2, right?In that case, how about this:
SELECT *
FROM table1 t1
LEFT JOIN table2 t2
ON (t2.ID1 = t1.ID1 AND t1.ID1 <> 'Unassigned')
OR (t2.ID2 = t1.ID2 AND t1.ID1 = 'Unassigned')
Ichiyo85, does this solution proposed by mthurber work for you?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 13, 2011 at 11:55 pm
ichiyo85 (4/13/2011)
ChrisM@home: Good question! Would you have any suggestions? That's actually one that I don't know what to do...
SQL Server - and the community who use it - are familiar with the use of NULL. Stick to convention, you will find programming far easier and getting support easier too. This thread is a good example for both.
If your boss prefers "Unassigned" to NULL, then convince him/her that it's best done at the output / reporting stage, not in the tables. Add weight to your argument by asking how to handle NULL values of numeric, boolean and date datatypes.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 14, 2011 at 7:17 am
ichiyo85 (4/13/2011)
Stefan Krzywicki: Thank you for your suggestions. I have 2 IDs because I need them both to connect to other tables. Also they are both important in our business.Also, I don't want NULLs because our boss likes the word "Unassigned" better than "NULL." I am sorry if this is a lame reason.
These are both Foreign keys? You don't want to be in the position of having to check to see if one ID is NULL before looking to another id. I'd suggest redesigning these tables so you can do a more straightforward join. If you'd like some assistance, post the table structures I'd be happy to take a look and see if I can help.
As far as your boss not liking NULL, does he not like it in the database or in reports? If he doesn't like it in the database, show him how with 'unassigned' you have to use the complex CASE statement where with NULL you can use Coalesce. Also let him know that if you have a column that is mostly NULL, you can save a lot of space with the SPARSE keyword.
If he doesn't like it in reports, just use the Coalesce statement to replace NULL with 'unassigned' when creating your output.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 14, 2011 at 2:42 pm
Jason: The code works, and it returns the same result as the coalesce.
I think my boss is more concerned with the output. Therefore I can probably set those "unassigned" values back to NULL again. Would I do that using "delete from"? It deletes the entire row and I only want to nullify specific cells. Please advise again.
Regarding the database design, I really can't change it. That's out of my control...
Thank you very much for all your help!
April 14, 2011 at 2:52 pm
You would use an update statement to set the fields to NULL
UPDATE YourTABLE
SET YourField = NULL
WHERE YourField = 'unassigned'
Don't forget your WHERE statement!
Then you can use COALESCE to return 'unassigned' from your data
SELECT COALESCE(YourField,'unassigned') FROM...
April 14, 2011 at 4:17 pm
ichiyo85 (4/14/2011)
Jason: The code works, and it returns the same result as the coalesce.I think my boss is more concerned with the output. Therefore I can probably set those "unassigned" values back to NULL again. Would I do that using "delete from"? It deletes the entire row and I only want to nullify specific cells. Please advise again.
Regarding the database design, I really can't change it. That's out of my control...
Thank you very much for all your help!
Chrissy321 has it exactly right.
Once you have them back as NULL, you'll still want to use subqueries, but you can change the CASE statements to COALESCE statements
CREATE TABLE #Table1(ID1 int, ID2 int)
CREATE TABLE #Table2(ID1 int, ID2 int)
SELECT * -- because I have no idea what other columns you want
FROM
(SELECT COALESCE(ID1, ID2) AS Table1ID
FROM #Table1) T1
left join (SELECT COALESCE(ID1, ID2) AS Table2ID
FROM #Table2) T2
on T1.Table1ID = T2.Table2ID
I think you'll find that much more readable.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 15, 2011 at 7:35 am
Thank you very much! It worked 😀
April 15, 2011 at 11:37 am
Ichiyo85, on the 12th you said:
"Also,
SELECT *
FROM table1 t1
LEFT JOIN table2 t2
ON t2.ID1 = t1.ID1
OR t2.ID1 = t1.ID2
OR t2.ID2 = t1.ID1
OR t2.ID2 = t1.ID2
WHERE t1.ID1 IS NOT NULL AND t2.ID1 IS NOT NULL
would not return what I need because I don't want to have t2.ID1 = t1.ID2 or t2.ID2 = t1.ID1 .
I want to join FIRST by t2.ID1 = t1.ID1 and if ID1 does not exist join using t2.ID2 = t1.ID2"
I think Stefan's code, which looks to me a lot like the code you originally posted, will give you matches where t2.ID2 = t1.ID1 and where t2.ID1 = t1.ID2.
Here is Stefan's code again, with one row of test data for each table. Is this result what you want?
CREATE TABLE #Table1(ID1 int, ID2 int)
CREATE TABLE #Table2(ID1 int, ID2 int)
INSERT #Table1 (ID1, ID2)
Values (1,2)
INSERT #Table2 (ID1, ID2)
Values (NULL,1)
SELECT * -- because I have no idea what other columns you want
FROM
(SELECT COALESCE(ID1, ID2) AS Table1ID
FROM #Table1) T1
left join (SELECT COALESCE(ID1, ID2) AS Table2ID
FROM #Table2) T2
on T1.Table1ID = T2.Table2ID
The result is a row where table1.id1 = table2.id2 as follows:
Table1IDTable2ID
1 1
April 15, 2011 at 11:54 am
mthurber (4/15/2011)
Ichiyo85, on the 12th you said:"Also,
SELECT *
FROM table1 t1
LEFT JOIN table2 t2
ON t2.ID1 = t1.ID1
OR t2.ID1 = t1.ID2
OR t2.ID2 = t1.ID1
OR t2.ID2 = t1.ID2
WHERE t1.ID1 IS NOT NULL AND t2.ID1 IS NOT NULL
would not return what I need because I don't want to have t2.ID1 = t1.ID2 or t2.ID2 = t1.ID1 .
I want to join FIRST by t2.ID1 = t1.ID1 and if ID1 does not exist join using t2.ID2 = t1.ID2"
I think Stefan's code, which looks to me a lot like the code you originally posted, will give you matches where t2.ID2 = t1.ID1 and where t2.ID1 = t1.ID2.
Here is Stefan's code again, with one row of test data for each table. Is this result what you want?
CREATE TABLE #Table1(ID1 int, ID2 int)
CREATE TABLE #Table2(ID1 int, ID2 int)
INSERT #Table1 (ID1, ID2)
Values (1,2)
INSERT #Table2 (ID1, ID2)
Values (NULL,1)
SELECT * -- because I have no idea what other columns you want
FROM
(SELECT COALESCE(ID1, ID2) AS Table1ID
FROM #Table1) T1
left join (SELECT COALESCE(ID1, ID2) AS Table2ID
FROM #Table2) T2
on T1.Table1ID = T2.Table2ID
The result is a row where table1.id1 = table2.id2 as follows:
Table1IDTable2ID
1 1
Ha! Good catch! That's what I get for looking for a solution based on his code and not by the stated requirement.
If you want where Table1.ID1 = Table2.ID2 and when that's not a match look for Table1.ID2 = Table2.ID2, that's different and won't involve COALESCE at all. I think I'd go with a temp table.
CREATE TABLE #Table1(ID1 int, ID2 int, anothercol varchar(20))
CREATE TABLE #Table2(ID1 int, ID2 int, anothercol varchar(20))
CREATE TABLE #Result(ID1 int, ID2 int, anothercol varchar(20))
INSERT INTO #Result(ID1, anothercol)
SELECT ID1, anothercol
FROM #Table1 T1
INNER JOIN #Table2 T2
ON T1.ID1 = T2.ID1
INSERT INTO #Result(ID2, anothercol)
SELECT ID2, anothercol
FROM #Table1 T1
INNER JOIN #Table2 T2
ON T1.ID2 = T2.ID2
WHERE T1.ID1 NOT IN (SELECT ID1 FROM #Result)
SELECT * FROM #Result
That will get you all the matches where Table1.ID1 matches Table2.ID1
and where Table1.ID2 matches Table2.ID2
but nothing where Table1.ID1 matches Table2.ID2
or Table1.ID2 matches Table2.ID1
It also makes sure you don't get the same match more than once if it happens to match on both ID1 and ID2.
Hope that works better for you.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 15, 2011 at 12:36 pm
I'd join table 2 twice, once on ID1 on both sides, and again on ID2 on both sides, then test for match in the output. Often, but not always, this approach yields better performance - sometimes much better - than a conditional join. It depends upon the complexity of the condition and of course if it's SARGable.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply