15,170,651 members
1.00/5 (1 vote)
See more:
Hi,

I have two tables like as follows.

Table 1 : `Matrix` Table
Table 2 : `Transaction` Table

SQL
```-- Matrix Table
CREATE TABLE [matrixtable] (
[prkey] [int] IDENTITY (1, 1) NOT NULL ,
[polid] [int] NULL ,
[clmtypeid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[insid] [int] NULL ,
CONSTRAINT [PK_matrixtable] PRIMARY KEY  CLUSTERED
(
[prkey]
)  ON [PRIMARY]
) ON [PRIMARY]
GO
-- Transaction table
CREATE TABLE [ClaimTable] (
[claimid] [int] NOT NULL ,
[polid] [int] NOT NULL ,
[insid] [int] NOT NULL ,
[clmtypeid] [int] NULL
) ON [PRIMARY]
GO

--Values for Matrx Table
INSERT INTO [ClaimTable](claimid,polid,insid,clmtypeid)
SELECT 101,1000,1,1
UNION ALL
SELECT 102,1000,1,2
UNION ALL
SELECT 103,1000,1,6

-- Value for Transaction Table
INSERT INTO matrixtable (polid,clmtypeid,insid)
SELECT 1000,'1,2,6',1
UNION ALL SELECT 1001,'3',1```

I need to join this two table based on `"clmtypeid"`.

Help me to solve this problem.
Posted
Updated 27-Dec-11 18:51pm
v3

## Solution 2

Here it is :

SQL
```select * from (
select prkey, claimid from
(
SELECT
prkey,
CAST('<r>' + REPLACE(clmtypeid, ',', '</r><r>') + '</r>' AS XML) claimXml
FROM matrixtable
) newmat
CROSS APPLY (
SELECT
CId.value('.', 'int') ClaimId
FROM newmat.claimXml.nodes('r') AS ClaimCodes(CId)
) Splited
) s inner join  claimtable c on s.ClaimId = c.clmtypeid```

Hope it helps.
gvprabu 21-Dec-11 1:16am

I need Query with out XML Data Type....
Amir Mahfoozi 21-Dec-11 1:19am

So you should have a user defined function to convert the comma separated IDs to a table and then join them to the claimtable.

## Solution 4

Hi,

At last I find the Solution,

SQL
```-- Actual Query
SELECT C.claimid,C.polid,C.insid,C.clmtypeid
FROM ClaimTable C
INNER JOIN matrixtable M ON M.polid=C.polid AND C.clmtypeid IN (SELECT Value FROM dbo.fnSplitString (M.clmtypeid,','))

-- Split Function
CREATE FUNCTION fnSplitString(@str nvarchar(max),@sep nvarchar(max))
RETURNS TABLE
AS
RETURN
WITH a AS(
SELECT CAST(0 AS BIGINT) as idx1,CHARINDEX(@sep,@str) idx2
UNION ALL
SELECT idx2+1,CHARINDEX(@sep,@str,idx2+1)
FROM a
WHERE idx2>0
)
SELECT SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1) as value
FROM a```

## Solution 3

To add to previous answers, consider adding a foreign key between the two tables: FOREIGN KEY Constraints[^]
gvprabu 21-Dec-11 1:16am

No FK Constrain and all....
Wendelius 21-Dec-11 1:22am

Adding the FK between the tables would help you to ensure the correctness so give it a thought :)

## Solution 1

you can join like this :
SQL
```select matrixtable.*,ClaimTable.* from matrixtable
inner join ClaimTable on ClaimTable.clmtypeid=matrixtable.clmtypeid```

and further you can add join condition if you have more common values. and you can get particular columns also like this :
SQL
```select matrixtable.prkey,matrixtable.polid,ClaimTable.claimid from matrixtable
inner join ClaimTable on ClaimTable.clmtypeid=matrixtable.clmtypeid```

Don't forget to mark as answer if it helps. :)
gvprabu 21-Dec-11 1:15am

Hi.....

Its not Direct Join...

How I will Join 1 = '1,2,6'..?

Pls check My Data....

## Solution 5

SQL
```select * from matrixtable m, ClaimTable c
where m.clmtypeid=c.clmtypeid```
v2
Karthik Harve 27-Dec-11 5:45am

 pre tags added.

## Solution 6

try this

SQL
```select mt.*,ct.* from matrixtable mt
inner join ClaimTable ct on ct.clmtypeid=mt.clmtypeid```

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 290 Dave Kreskowiak 100 Richard Deeming 60 CPallini 60 LesF 55
 OriginalGriff 3,623 Richard MacCutchan 1,185 CHill60 895 _Asif_ 713 CPallini 653

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900