Cross Table Query - SQL Server Forums

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)

Cross Table Query

Author

Topic

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2013-08-27 : 04:07:18

Hi every one,

I have two tables and I want to write a join select statement, in table "A" I have a column contains list of numbers with full length e.g

0012342345
0014567884
0016543219
0018987654

in table "B" I have codes e.g.

001
0014
00165
0018987

now I want to join both table through "LIKE" operator but I want my select statement bring me the results where maximum characters match.

If I write my query like this:

select A.Numbers, B.Codes From TableA A, TableB B
where A.Numbers like B.Codes + '%'

The results contains duplicate records. Please help.

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-08-27 : 04:45:33

[code]
;With tableA
AS
(
select '0012342345' as [Numbers] union all
select '0014567884' union all
select '0016543219' union all
select '0018987654'
)
,tableB AS
(
select '001' as [Codes] union all
select '0014' union all
select '00165' union all
select '0018987' )

select Q.Numbers
,Q.Codes
from (
select A.Numbers
,B.Codes
,ROW_NUMBER() OVER (PARTITION BY A.Numbers ORDER BY len(B.Codes) desc) as rn
From TableA A, TableB B
where A.Numbers like B.Codes + '%' )Q
where Q.rn =1

[/code]

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-27 : 04:46:11

select A.Numbers, MAX(B.Codes) From TableA A, TableB B
where A.Numbers like B.Codes + '%'
GROUP BY A.Numbers

--
Chandu

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-08-27 : 04:55:34

[code]

select
A.Numbers
,B.Codes
from tableA A
outer apply
(select top 1
B.Codes
from tableB B
where A.Numbers like B.Codes + '%'
order by len(B.Codes) desc )B

[/code]

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2013-08-27 : 07:42:23

These queries are taking too much time as I have big DB ... Please provide query which can optimize performance as well.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-27 : 07:54:35

--What about this one
select A.Numbers, MAX(B.Codes) From TableA A, TableB B
where A.Numbers like B.Codes + '%'
GROUP BY A.Numbers

Actually LIKE operator itself takes too much time

--
Chandu

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-27 : 12:32:32

Stepson's solution is going to be the most reliable and performant, but you'll want a clustered index on Number and a clustered index on Code at a minimum. If you have flexibility in your design, you can either create a computed column on LEN(Code) and add an index on it or you can create an indexed view with the same to give you the best plan.

IF OBJECT_ID('tempdb..#FullNumber') IS NOT NULL DROP TABLE #FullNumber;
IF OBJECT_ID('tempdb..#PartialNumber') IS NOT NULL DROP TABLE #PartialNumber;

CREATE TABLE #FullNumber	(Number VARCHAR(10), PRIMARY KEY (Number) WITH(IGNORE_DUP_KEY=ON));
CREATE TABLE #PartialNumber (Code VARCHAR(10), CodeLEN AS LEN(Code), PRIMARY KEY (Code) WITH(IGNORE_DUP_KEY=ON));
GO

INSERT #FullNumber
SELECT RIGHT(REPLICATE('0',9) + CONVERT(VARCHAR(10),ABS(CHECKSUM(NEWID()))),10) AS RandomNumber
GO 100000

INSERT #PartialNumber
SELECT RIGHT(REPLICATE('0',9) + CONVERT(VARCHAR(10),ABS(CHECKSUM(NEWID()))),CONVERT(INT,RAND() * 10 + 1) )
GO 10000

CREATE NONCLUSTERED INDEX ix_#PartialNumber_CodeLEN on #PartialNumber(CodeLEN);

SELECT f.Number, c.Code
FROM #FullNumber as f
CROSS APPLY (SELECT TOP 1 p.Code FROM #PartialNumber as p WHERE f.Number LIKE p.Code + '%' ORDER BY CodeLEN DESC) as c;

DROP INDEX ix_#PartialNumber_CodeLEN on #PartialNumber;

SELECT f.Number, c.Code
FROM #FullNumber as f
CROSS APPLY (SELECT TOP 1 p.Code FROM #PartialNumber as p WHERE f.Number LIKE p.Code + '%' ORDER BY CodeLEN DESC) as c;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources