Interesting SQL statement

[Kelvin HK]

Hello all,

I have an interesting SQL question, struggling for a long time but still can't find an answer yet. The sample SQL is as follow:

CREATE TABLE #Table1
( Table1_ID INT
)

CREATE TABLE #Table2
( Table1_ID INT,
Table2_Content NVARCHAR(100)
)

CREATE TABLE #Table3
( Table1_ID INT,
Table3_Content NVARCHAR(100)
)

INSERT INTO #Table1(Table1_ ID) values (1)
INSERT INTO #Table2(Table1_ ID, Table2_Content) values (1, 'Table2_A)
INSERT INTO #Table2(Table1_ ID, Table2_Content) values (1, 'Table2_B)
INSERT INTO #Table3(Table1_ ID, Table3_Content) values (1, 'Table3_A)
INSERT INTO #Table3(Table1_ ID, Table3_Content) values (1, 'Table3_B')

So, the question is how can I get the result:

1     Table2_A     Table3_A
1     Table2_B     Table3_B

Please note that number of records in Table2 and Table3 for the same Table1_ID may be different. For example, there is one additional value Table2_C in Table2, the result should look like:

1     Table2_A     Table3_A
1     Table2_B     Table3_B
1     Table2_C     NULL

Vice versa, it may look like:

1     Table2_A     Table3_A
1     Table2_B     Table3_B
1     NULL         Table3_C

Highly appreciate any kind of help, thanks in advance!

Regards,
Kelvin

[deepuv04]

Hello all,

I have an interesting SQL question, struggling for a long time but still can't find an answer yet. The sample SQL is as follow:

CREATE TABLE #Table1
( Table1_ID INT
)

CREATE TABLE #Table2
( Table1_ID INT,
Table2_Content NVARCHAR(100)
)

CREATE TABLE #Table3
( Table1_ID INT,
Table3_Content NVARCHAR(100)
)

INSERT INTO #Table1(Table1_ ID) values (1)
INSERT INTO #Table2(Table1_ ID, Table2_Content) values (1, 'Table2_A)
INSERT INTO #Table2(Table1_ ID, Table2_Content) values (1, 'Table2_B)
INSERT INTO #Table3(Table1_ ID, Table3_Content) values (1, 'Table3_A)
INSERT INTO #Table3(Table1_ ID, Table3_Content) values (1, 'Table3_B')

So, the question is how can I get the result:

1     Table2_A     Table3_A
1     Table2_B     Table3_B

Please note that number of records in Table2 and Table3 for the same Table1_ID may be different. For example, there is one additional value Table2_C in Table2, the result should look like:

1     Table2_A     Table3_A
1     Table2_B     Table3_B
1     Table2_C     NULL

Vice versa, it may look like:

1     Table2_A     Table3_A
1     Table2_B     Table3_B
1     NULL         Table3_C

Highly appreciate any kind of help, thanks in advance!

Regards,
Kelvin

Hi,
use the following query:
(this query is created based on the sample data given)

SELECT P.Table1_ID,Tab le2_Content,Tab le3_Content
FROM #Table1 P FULL JOIN
#Table2 A FULL JOIN
#Table3 B ON ISNULL(SUBSTRIN G(A.Table2_Cont ent,LEN(A.Table 2_Content)- 1,LEN(A.Table2_ Content)),B.Tab le3_Content) =
ISNULL(SUBSTRIN G(B.Table3_Cont ent,LEN(B.Table 3_Content)- 1,LEN(B.Table3_ Content)),A.Tab le2_Content)
ON P.Table1_ID IN (A.Table1_ID,B. Table1_ID)

[Kelvin HK]

Thanks so much, it works!