Geek asks and answers

Join by value to another table

Hi everyone, I have a table TableCthat stores primary key values ​​from two different tables TableAand TableB. because they are the primary keys from two tables, I could get duplicates in this TableC, so when I save the values, I prefix the Primary keys with short text to distinguish what the value comes from this table.

Now I would like to join this TableCwith TableAand TableBto get the data from TableAandTableB

TableC : 

ID_Column 
1A
1B
2A

TableA: 

ID_Column   |   Data
1           |   data A 1
2           |   data A 2
3           |   data A 3

TableB: 

ID_Column   |   Data
1           |   data B 1
2           |   data B 2
3           |   data B 3

This is what I tried to do.

select C.ID_Column, data
from tableC C
      inner join tableA A
on A.ID_Column = left(C.ID_Column, 1)
      inner join tableB B
on B.ID_Column = left(C.ID_Column, 1)

. b, ID_Column B TableA, ID_Column A

.

+3
2

, TableC 2 , id , , . , :

SELECT  C.ID_Column,
        ISNULL(A.Data,B.Data) Data
FROM TableC C
LEFT JOIN TableA A
    ON LEFT(C.ID_Column,LEN(C.ID_Column)-1) = A.ID_Column
    AND RIGHT(C.ID_Column,1) = 'A'
LEFT JOIN TableB B
    ON LEFT(C.ID_Column,LEN(C.ID_Column)-1) = B.ID_Column
    AND RIGHT(C.ID_Column,1) = 'B'

:

╔══════════╦══════════╗
β•‘ D_COLUMN β•‘   DATA   β•‘
╠══════════╬══════════╣
β•‘ 1A       β•‘ data A 1 β•‘
β•‘ 1B       β•‘ data B 1 β•‘
β•‘ 2A       β•‘ data A 2 β•‘
β•šβ•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•

.

+2

, , , C. , ( ).

--Create Table A
IF OBJECT_ID('tempdb..#TableA') IS NOT NULL DROP TABLE #TableA
CREATE TABLE #TableA (ID_COLUMN INT, DATA VARCHAR(max))
INSERT INTO  #TableA(ID_COLUMN,DATA) VALUES (1,'data A 1'),(2,'data A 2'),(3,'data A 3')

--Create Table B
IF OBJECT_ID('tempdb..#TableB') IS NOT NULL DROP TABLE #TableB
CREATE TABLE #TableB (ID_COLUMN INT, DATA VARCHAR(max))
INSERT INTO #TableB (ID_COLUMN,DATA) VALUES (1,'data B 1'),(2,'data B 2'),(3,'data B 3')

--Create Table C
IF OBJECT_ID('tempdb..#TableC') IS NOT NULL DROP TABLE #TableC
CREATE TABLE #TableC (ID_COLUMN INT, ORIGIN_TABLE CHAR(1))
INSERT INTO #TableC (ID_COLUMN, ORIGIN_TABLE) VALUES (1,'A'), (1,'B'),(2,'A')

SELECT  A.ID_COLUMN, A.DATA
FROM       #TableC AS C
       INNER JOIN #TableA AS A ON (C.ID_COLUMN = A.ID_COLUMN)
WHERE   C.ORIGIN_TABLE = 'A'
UNION ALL
SELECT  B.ID_COLUMN, B.DATA
FROM       #TableC AS C
       INNER JOIN #TableB AS B ON (C.ID_COLUMN = B.ID_COLUMN)
WHERE   C.ORIGIN_TABLE = 'B'
0
source

More articles:


All Articles