CODEWITHSUNDEEP
sql
detweiller
detweiller

Reputation: 3

How can I query this in a database?

I have 2 tables

Table 1

Positon | Name | Nickname | Address
------------------------------------
   1    |   2  |    3     |    4
   5    |   6  |          |    7
   5    |   6  |          |    8

Table 2

Detail_seq | Details
--------------------
     1     |  manager
     2     |  Jimmy
     3     |  Jim
     4     |  Chicago
     5     |  Supervisor
     6     |  Mike
     7     |  Vancouver
     8     |  New York

and the output should be like this or something

Position: Manager
Name: Jimmy
Nickname: Jim
Address: Chicago

Position: Supervisor
Name: Mike
Nickname:
Address: Vancouver and New York

feel free to ask questions if it is not clear.

thanks in advance

Upvotes: 0

Views: 75

Answers (4)

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

You need to join the details table one time for every detail:

  SELECT pos.Details, name.details, nick.details, adress.details
    FROM table1 t1
    LEFT JOIN table2 pos     ON pos.detail_seq = t1.position
    LEFT JOIN table2 name    ON name.detail_seq = t1.name
    LEFT JOIN table2 nick    ON nick.detail_seq = t1.nickname
    LEFT JOIN table2 address ON address.detail_seq = t1.address

Upvotes: 1

Aspirant
Aspirant

Reputation: 2278

select (select details from t2 where t2.detail_seq=t1.position) position,
       (select details from t2 where t2.detail_seq=t1.name) Name,
       (select details from t2 where t2.det_seq=t1.nickname) nickname,
       (select details from t2 where t2.det_seq=t1.address) address
from t1 ;

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460138

I think this is what you want:

SELECT Position=manager.details, 
       Name=Name.details, 
       Nickname=Nickname.details, 
       Address=Address.details 
FROM   table1 t1 
       LEFT OUTER JOIN table2 manager 
                    ON t1.positon = manager.detail_seq 
       LEFT OUTER JOIN table2 name 
                    ON t1.name = name.detail_seq 
       LEFT OUTER JOIN table2 Nickname 
                    ON t1.nickname = Nickname.detail_seq 
       LEFT OUTER JOIN table2 Address 
                    ON t1.address = Address.detail_seq

DEMO

Upvotes: 1

Doruk
Doruk

Reputation: 914

It's a little bit extreme to store data this way. I can offer you to store the data categories in different tables. but if you insist, this should work

SELECT d1.details, d2.details, d3.details, d4.details
FROM main m, details d1, details d2, details d3, details d4
WHERE m.position = d1.detail_seq 
AND   m.name     = d2.detail_seq
AND   m.nickname = d3.detail_seq
AND   m.adress   = d4.detail_seq

Upvotes: 1

Related Questions