Make a JOIN of TWO Tables without any Matched Column

Question

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 19-Dec-11 23:24pm

gvprabu

Updated 27-Dec-11 17:51pm

v3

Add a Solution

6 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Amir Mahfoozi · Answer 1 · 2011-12-19T23:51:00

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.

Posted 19-Dec-11 23:51pm

Amir Mahfoozi

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.

gvprabu · Answer 2 · 2011-12-26T23:01:00

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

Wendelius · Answer 3 · 2011-12-20T11:07:00

Solution 3

To add to previous answers, consider adding a foreign key between the two tables: FOREIGN KEY Constraints[^]

Posted 20-Dec-11 11:07am

Wendelius

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 :)

Manish.Insan · Answer 4 · 2011-12-19T23:50:00

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. :)

Tanveer A · Answer 5 · 2011-12-26T23:05:00

Solution 5

SQL

select * from matrixtable m, ClaimTable c
where m.clmtypeid=c.clmtypeid

Posted 26-Dec-11 23:05pm

Tanveer A

Updated 26-Dec-11 23:44pm

v2

Comments

Karthik Harve 27-Dec-11 5:45am

[Edit] pre tags added.

Sarvesh Kumar Gupta · Answer 6 · 2011-12-27T17:56:00

Solution 6

try this

SQL

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

Posted 27-Dec-11 17:56pm

Sarvesh Kumar Gupta

Make a JOIN of TWO Tables without any Matched Column

6 solutions

Solution 2

Solution 4

Solution 3

Solution 1

Solution 5

Solution 6

Add your solution here

Preview 0