Which SQL clause should be used?

Question

I have a table like this in Firebird:

Tablename: USERNAMES


+--------+-----+
|username|code |
+--------+-----+
|a       |1    |
+--------+-----+
|b       |2    |
+--------+-----+
|c       |3    |
+--------+-----+
|d       |4    |
+--------+-----+
|e       |5    |
+--------+-----+

and this table

Tablename: SERVICES
+-----------+-----+-----+-----+
|serviceno  |user1|user2|user3|
+-----------+-----+-----+-----+
|v1         |     |1    |2    |
+-----------+-----+-----+-----+
|v2         |3    |2    |     |
+-----------+-----+-----+-----+
|v3         |5    |4    |     |
+-----------+-----+-----+-----+
|v4         |3    |2    |1    |
+-----------+-----+-----+-----+

I want that table as

    +-----------+-----+-----+-----+
    |serviceno  |user1|user2|user3|
    +-----------+-----+-----+-----+
    |v1         |     |a    |b    |
    +-----------+-----+-----+-----+
    |v2         |c    |b    |     |
    +-----------+-----+-----+-----+
    |v3         |e    |d    |     |
    +-----------+-----+-----+-----+
    |v4         |c    |b    |a    |
    +-----------+-----+-----+-----+

I've tried that

 SELECT IFF(A.USER1 = NULL,NULL,B.NAME),
    IFF(A.USER2 = NULL,NULL,C.NAME),
    IFF(A.USER3 = NULL,NULL,D.NAME)
     FROM SERVICES A INNER JOIN USERNAMES B ON (A.USER1 =B.CODE)
    INNER JOIN USERNAMES C ON (A.USER2 =C.CODE)
    INNER JOIN USERNAMES D ON (A.USER3 =D.CODE)

And several attempts too,but allways the result is only row v4. why? and how can i get all rows?

Answer 1

I'm not familiar with Firebird, but here's a generic SQL solution:

select
    serviceno,
    (select username
       from usernames
      where services.user1 = usernames.code) as user1,
    (select username
       from usernames
      where services.user2 = usernames.code) as user2,
    (select username
       from usernames
      where services.user3 = usernames.code) as user3
from
    services