Konstantin Rybakov Konstantin Rybakov - 1 year ago 124
SQL Question

Mandatory disjoint: beautiful query?

I have a schema with table
user (username, password, fullname, usertype)

there are 4 types of users and for each there is a table with additional attributes for specific type:

  • individual(username (FOREIGN), education, work_since)

  • corporation*(username (FOREIGN), headquarters, office, num_employees)

  • and a couple more...

there could be only 1 record in all the additional tables for user.

I need to display all of the user information from user table and additional attribute table based on user type.

The first thing that came to mind was to first query user table, and then, based on type returned, query one of the related tables... but that would be too many queries, so I was wondering, is it possible to do it in a single query?

Answer Source

Use left join:

select u.*,
       (case when i.username is not null then 'individual'
             when c.username is not null then 'corporation'
        end) as usertype,
       i.education, i.work_since,
       c.headquarters, c.office, c.num_employees
from users u left join
     individual i
     on i.username = u.username left join
     corporation c
     on c.username = u.username;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download