Hi all:

Consider a hierarchy of users. All users have certain properties (they all have a name and date-of-birth). Additionally, based on their type, users have more properties. So a graduate user may have information regarding the year and school of graduation, a professor user may have information about the classes they teach etc.

A intuitive way (for me at least) to model this is to create a table per type and add a special type discriminator field that points to the next special table for that type. So, I have a "User" table that holds information common to all users and then specialized tables such as "GradUser", "UndergradUser" etc., that hold additional specific information about that particular kind of user.

Table "User" has columns: --userid --name --birthdate --usertype

Table "GradUser" has columns: --userid --school

Table "ProfessorUser" has columns: --userid --classes

and Table "UserType" has 2 entries: usertype table description 1 GradUser type for grad 2 ProfUser type for prof's

Taking a concrete example, say one user-record (shown vertically) looks like:

Table: User Column Value userid 123 name Molly Millions usertype 1

Table: GradUser id 123 school Hard-Knocks

So based on the usertype ('1' in the above example), I have to get the remaining information for Molly from "GradUser". If the usertype had been '2', I would then have had to get the remaining information from the "ProfUser" table instead.

Note, I can't do a simply join between "User" and "GradUser" (using "123"), because I don't know the name of the table ("GradUser" or "ProfUser") until I have read the "usertype" field in the "User" table.

So what I really need to do is: -Read a record from User -Read it's corresponding type. -Based on the type, choose another table and read additional information.

MySQL does not have sub-queries but does allow me to select into temporary tables easily.

What's the most efficient SQL to read a given user's entire information (common + specific information) ? I want to minimize traffic between my client and the server so I would ideally speaking, like to do this all on the database side itself. Essentially I need a "if .. then" type of logic on the *server*. So in psuedo-code:

if (usertype = '1') then join user and graduser else if (usertype = '2') then join user and profuser etc..

Best regards,