联接两个没有任何匹配列的表 [英] Make a JOIN of TWO Tables without any Matched Column

问题描述

我有两个如下表.

表1:Matrix表
表2:Transaction表

Hi,

I have two tables like as follows.

Table 1 : Matrix Table
Table 2 : Transaction Table

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

我需要基于"clmtypeid"加入这两个表.

提前谢谢.

帮助我解决此问题.

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

Advance Thanks.

Help me to solve this problem.

推荐答案

在这里是:

Here it is :

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.

您好，

终于找到了解决方法，

Hi,

At last I find the Solution,

-- 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[^]

这篇关于联接两个没有任何匹配列的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！