CODEWITHSUNDEEP
sql-server
DeveloperSD
DeveloperSD

Reputation: 316

SQL Query to fetch record based on existing column value

I want to write SQL query to retrieve below data.

enter image description here

Table1->ID_1 / ID_2 consist of primary key of Table2

Based on this in expected result required Value1 & Value2. At code level am retrieving information from Table1 & by iterating through it execute another query to fetch record for Value1 / Value2.

I want to write SQL query to get entire data in one attempt.

Upvotes: 1

Views: 1464

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Try this working example:

DECLARE @Table1 TABLE([User] VARCHAR(100),Data VARCHAR(100),ID_1 INT,ID_2 INT);
INSERT INTO @Table1 VALUES('Admin','data',1,2)
                         ,('test','data',3,2) 

DECLARE @Table2 TABLE(ID INT,Value VARCHAR(100));
INSERT INTO @Table2 VALUES(1,'ABC')
                         ,(2,'PQR')
                         ,(3,'XYZ');  
SELECT t1.[User]
      ,t1.Data
      ,t1.ID_1
      ,t2a.Value
      ,t1.ID_2
      ,t2b.Value
FROM @Table1 AS t1
LEFT JOIN @Table2 AS t2a ON t1.ID_1=t2a.ID
LEFT JOIN @Table2 AS t2b ON t1.ID_2=t2b.ID

The result

User    Data    ID_1    Value   ID_2    Value
Admin   data    1       ABC     2       PQR
test    data    3       XYZ     2       PQR

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You can join the table_1 twice with table 2 on the two id columns:

select t1.*, 
    t2_1.value as value1,
    t2_2.value as value2
from table_1 t1
join table t2_1 on t1.id_1 = t2_1.id
join table t2_2 on t1.id_2 = t2_2.id;

If any of the id column in table_1 is nullable, change the inner join to left. Something like:

select t1.*, 
    t2_1.value as value1,
    t2_2.value as value2
from table_1 t1
left join table t2_1 on t1.id_1 = t2_1.id
left join table t2_2 on t1.id_2 = t2_2.id;

Upvotes: 1

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

You Just Need to Join the Table2 twice

SELECT
    T1.[User],
    T1.[Data],
    T1.ID_1,
    Value1 = T2.Value,
    T1.ID_2,
    Value2 = T3.Value
    FROM Table1 T1
       INNER JOIN Table2 T2
          ON T1.ID_1 = T2.ID
       INNER JOIN Table2 T3
          ON T1.ID_2 = T3.ID

Upvotes: 0

Related Questions