[Solved] SQL — SELECT 3 TABLES WITH 2 IDs

by


This is a very bad data model. Change it if you can.

If there is a column gender in the client table, why muddle through with some generic list? Just add a table gender and link to its rows with client.gender_id:

  • table gender (gender_id, description)
  • table client (client_id, name, gender_id)

If you really must make this generic and are ready to live with the consequences (slower access, no guaranteed consistency, …), then remove the gender column and add a table client_attribut instead, consisting of client_id, list_id and list_value.

Anyway, with your current design the query would be

select c.clientid, c.name, la.listlabel
from client c
join list_attribute la 
  on la.listvalue = c.gender
  and la.listid = (select listid from list where listname="Gender")
order by c.clientid;

1

solved SQL — SELECT 3 TABLES WITH 2 IDs