Click here to Skip to main content
15,170,651 members
Please Sign up or sign in to vote.
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".

Advance Thanks.

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

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.
   
Comments
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.
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
   
To add to previous answers, consider adding a foreign key between the two tables: FOREIGN KEY Constraints[^]
   
Comments
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 :)
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


Hope this will help you.
Don't forget to mark as answer if it helps. :)
   
Comments
gvprabu 21-Dec-11 1:15am
   
Hi.....

Its not Direct Join...

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

Pls check My Data....
SQL
select * from matrixtable m, ClaimTable c
where m.clmtypeid=c.clmtypeid
   
v2
Comments
Karthik Harve 27-Dec-11 5:45am
   
[Edit] pre tags added.
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)




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