December 20, 2011 at 7:55 am
SELECT *
FROM table1 t1
INNER JOIN table2 t2
on t1.col = t2.col
OR t1.col = '0' + t2.col
How can I prioritize this JOIN? That is, if I find an exact match (t1.col = t2.col), then I don't want to match on the other (t1.col = '0' + t2.col). I only want to match on the '0' if there is no exact match. I do not want to get both, either.
Thanks for any insight!
December 20, 2011 at 8:02 am
Left-join table2 twice, once for each condition. Resolve the dispute in the WHERE clause and the output using CASE.
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]
December 20, 2011 at 8:02 am
The easiest way I can think of to do that would be to add:
SELECT *
FROM table1 t1
INNER JOIN table2 t2
on t1.col = t2.col
OR t1.col = '0' + t2.col
and not exists (select * from table2 where t1.col = t2.col);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 20, 2011 at 12:23 pm
I'm not usre of your actual data since none was supplied, but if GSquared's solution doesn't work perhaps you can use this:
DECLARE @T1 TABLE (Col VARCHAR(2))
DECLARE @T2 TABLE (Col VARCHAR(2))
--INSERT @T1 (Col) VALUES ('1'), ('2')
INSERT @T1 (Col) VALUES ('01'), ('2')
INSERT @T2 (Col) VALUES ('1'), ('01'), ('2'), ('3')
-- GSquared
SELECT *
FROM @T1 t1
INNER JOIN @T2 t2
on t1.col = t2.col
OR t1.col = '0' + t2.col
and not exists (select * from @T2 where t1.col = t2.col);
-- Lamprey
SELECT *
FROM
(
SELECT
t1.col AS Col1,
t2.col AS Col2,
ROW_NUMBER() OVER (PARTITION BY t1.col ORDER BY case when t1.col = t2.col then 1 else 2 end ASC) as RowNum
FROM @T1 t1
INNER JOIN @T2 t2
on t1.col = t2.col
OR t1.col = '0' + t2.col
) AS T
WHERE RowNum = 1
December 20, 2011 at 1:14 pm
Thanks! I'm getting closer, but not sure if i'm there yet.
Here's a good example of what I'm dealing with:
-------------------------------------------------
drop table main
create table main(
col1 char(10),
col2 varchar(100))
INSERT INTO main VALUES('101','shshsfhgs')
INSERT INTO main VALUES('102','ertqehwrgs')
INSERT INTO main VALUES('0102','witpipqcqp')
INSERT INTO main VALUES('0103','retrtyhwqpp')
drop table #temp1
create table #temp1
(ref1 char(10),
refdata varchar(100))
drop table #temp2
Create table #temp2
(ref1 char(10),
refdata varchar(100))
insert into #temp1 values(101,'aaaaaaaaaaaaaa')
insert into #temp1 values(102,'bbbbbbbbbbbbbb')
insert into #temp1 values(103,'cccccccccccccc')
------------------------------------------
what I would like to see INSERTed into #temp2 is:
101 'aaaaaaaaaaaaaa'
102 'bbbbbbbbbbbbbb'
103 'cccccccccccccc'
See the main table has a 102 AND a 0102, but since the temp1 table matches on the 102, it doesn't update the 0102. The 103, on the other hand, has no match to 103, so it updates the 0103.
Does this make sense?
December 20, 2011 at 1:19 pm
The version I posted will do what you need, but it might have performance issues, mainly depending on data size.
What you're probably better off doing is a two-step insert into a temp table. First, the ones with the top priority, then any with the second priority that aren't already in there.
Test both ideas, see which performs best on your data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 20, 2011 at 1:44 pm
With the scenario I have, I'm still getting an extra unwanted row.
-------------------------------------
drop table main
create table main(
col1 char(10),
col2 varchar(100))
INSERT INTO main VALUES('101','shshsfhgs')
INSERT INTO main VALUES('102','ertqehwrgs')
INSERT INTO main VALUES('0102','witpipqcqp')
INSERT INTO main VALUES('0103','retrtyhwqpp')
drop table #temp1
create table #temp1
(ref1 char(10),
refdata varchar(100))
drop table #temp2
Create table #temp2
(ref1 char(10),
refdata varchar(100))
insert into #temp1 values(101,'aaaaaaaaaaaaaa')
insert into #temp1 values(102,'bbbbbbbbbbbbbb')
insert into #temp1 values(103,'cccccccccccccc')
select * from #temp2
insert into #temp2
SELECT t1.col1, #temp1.refdata
FROM main t1
INNER JOIN #temp1 on (t1.col1 = #temp1.ref1 )
OR (t1.col1 = '0' + #temp1.ref1
AND NOT EXISTS(select 1
FROM #temp1 b WHERE t1.col1 = b.ref1))
-------------------------------------
It's giving me this:
ref1refdata
101 aaaaaaaaaaaaaa
102 bbbbbbbbbbbbbb
0102 bbbbbbbbbbbbbb
0103 cccccccccccccc
and I don't want the 0102 row
December 20, 2011 at 2:07 pm
Yeah, GSquared solution won't work with your sampel data, that's why I posted an alternate solution.
Try this:SELECT *
FROM
(
SELECT
t1.ref1,
t1.refdata,
t2.col1,
ROW_NUMBER() OVER (PARTITION BY t1.ref1 ORDER BY case when t1.ref1 = t2.col1 then 1 else 2 end ASC) as RowNum
FROM #temp1 t1
INNER JOIN main t2
on t1.ref1 = t2.col1
OR '0' + t1.ref1 = t2.col1
) AS T
WHERE RowNum = 1
December 20, 2011 at 2:14 pm
That did it!!! Thank you so much!
😀
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply