Click here to Skip to main content
15,445,560 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Can someone help me to write SQL query to map ID from one table and get value from another table

Table1:
|ID |      Value        |
|---|-------------------|
|1  | America           |
|2  | Belgium           |
|3  | Canada            |
|4  | India             |
|5  | Australia, Africa |

Table2:  
|ID      |
|--------|
|[2,4]   |
|[1,4,2] |
|[1,5]   |

    SELECT *
    FROM Table1 a
    INNER JOIN Table2 b
    ON a.id=b.id

can someone help me to get result as below.
Output:   
|Value                         |
|------------------------------|
|[Belgium, India]              |
|[America, India, Belgium]     |
|[America, [Australia, Africa]]|


What I have tried:

I Have to write a query but not sure how we can join two table and get result as expected
Posted
Updated 19-Jun-22 21:26pm

1 solution

I'd say that you need to rethink your DB design: you seem to be trying to store multiple values in a single column of Table2 and assuming that they can be "back tracked" to Table1 easily. They can't, as the only way to store multiple values is via a string based column, and that means that each time you want to use them, you need to break up that string, convert the broken up bits into the same datatype as the ID column in Table1 (which is likely INT, probably IDENTITY) and store it in a temporary table for JOINing. While that is possible (Converting comma separated data in a column to rows for selection[^]) it's very messy, very inefficient, and a PITA to maintain later.

Instead, look at your actual tables and the data they store - I doubt if it's as simple as you show - and work out how you need to store multiple values so that can be managed better.
I often end up with a third table which adds multiple items:
Table1 as yours,
Table2:
ID   INT, IDENTITY (or UNIQUEVALUE) identifies row, not teh same as your ID column
... other columns ...

Table3:
Table2ID
Tabel1ID
That way, you can have as many or as few relationships between Table1 and Table2 as necessary for each row, and you can establish formal "one-to-many" relationships between your tables to enforce relational integrity when you make changes.
 
Share this answer
 

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