0

staff table: 

      id     name
       1     tony
       2     sony
       3     bony
       4     rony
etc.

xyz table:

current staff, next staff, hod staff, vp staff.
      1          2           3          4
      2          3           1          4
      4          3           2          1
etc.

when is run 

`select * from xyz,`

I need a query which instead of giving me id it will actually give me names of that staff from staff table.

Lucky
  • 16,787
  • 19
  • 117
  • 151
tony
  • 41
  • 3
  • possible duplicate of [SQL JOIN and different types of JOINs](http://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) – nkorth Aug 03 '15 at 09:40

2 Answers2

1

You need to join staff table more than once

SELECT b.name AS current_staff,
       d.name AS next_staff,
       c.name AS hod_staff,
       e.name AS vp_staff
FROM   xyz a
       INNER JOIN staff b
               ON a.current_staff = b.id
       INNER JOIN staff c
               ON a.hod_staff = c.id
       INNER JOIN staff d
               ON a.next_staff = d.id
       INNER JOIN staff e
               ON a.vp_staff = e.id
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
1

If I understand you right, I guess you just could use a multiple join here:

select a.name, b.name, c.name, d.name from xyz
        inner join staff as a on a.id = xyz.currentstaff
        inner join staff as b on b.id = xyz.nextstaff
        inner join staff as c on c.id = xyz.hodstaff
        inner join staff as d on d.id = xyz.vpstaff
Lucky
  • 16,787
  • 19
  • 117
  • 151
kl78
  • 1,628
  • 1
  • 16
  • 26