CODEWITHSUNDEEP
sqldatabaseoracle-database
Mallesh
Mallesh

Reputation: 37

retrieve lookup column twice with associated rows

I have two tables. First one

farmer_id         Farmer_name
   1               Raju
   2               Jay
   3               Ram
   5               Vinay

Second one 

farmer_id      registered_farmer_id  Season
  1                  2               2014-15
  3                  5               2015-16

Table one is the look up table which looks for the name of the farmer. Table two has two columns namely farmer_id and registered-farmer_id those look up for the same table. i.e table 1. What I need is

Farmer_id    farmer_name    Registered_farmer_id   Registered_farmer_name
  1           Raju                  2                   Jay
  3           Ram                   5                   Vinay

Upvotes: 1

Views: 54

Answers (4)

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

In MS SQL 2005+, you can use CROSS APPLY:

SELECT A.farmer_id, A.Farmer_name, B.farmer_id, B.Farmer_name
FROM (
      SELECT T1.farmer_id, T2.Farmer_name, T3.Registered_farmer_id
      FROM Table1 T1
         INNER JOIN Table T2
            ON T1.farmer_id = T2.farmer_id
     ) AS A
CROSS APPLY (
      SELECT farmer_id, Farmer_name
      FROM Table1
      WHERE farmer_id = A.Registered_farmer_id
      ) AS B

In Oracle, you use INNER JOIN instead:

SELECT A.farmer_id, A.Farmer_name, B.farmer_id, B.Farmer_name
FROM (
      SELECT T1.farmer_id, T2.Farmer_name, T3.Registered_farmer_id
      FROM Table1 T1
         INNER JOIN Table T2
            ON T1.farmer_id = T2.farmer_id
     ) AS A
INNER JOIN (
      SELECT farmer_id, Farmer_name
      FROM Table1
      ) AS B
WHERE B.farmer_id = A.Registered_farmer_id

Upvotes: 1

Naim Salameh
Naim Salameh

Reputation: 417

lets say your tables are called Table1(the lookup) and Table2

select t1.NAME fname, t2.NAME rfname from Table1 t1, Table1 t2, Table2 where t1.id = table1.ID and t2.id = table1.ID2 ;

Upvotes: 0

user3331966
user3331966

Reputation: 152

SELECT T2.farmer_id, T1.farmer_name, T2.registered_farmer_id, T3.farmer_name
FROM TABLE2 T2 
LEFT JOIN TABLE1 T1
ON T2.farmer_id = T1.farmer_id
LEFT JOIN TABLE1 T3
ON T2.registered_farmer_id = T3.farmer_id;

With TABLE1 the name of table 1 and TABLE2 the name of table 2.

Upvotes: 2

Mahmud
Mahmud

Reputation: 64

if your two tables are called from 2 different database tables; then you should change the second table's id with a different name and join both table. and finally you can retrieve from the merged table.

Upvotes: 0

Related Questions