CORRECTION: SELECT user.*,graduser.* FROM graduser RIGHT JOIN user USING(userid); SELECT user.*,profuser.* FROM profuser RIGHT JOIN user USING(userid);
-----Original Message----- From: Rick Emery [mailto:[EMAIL PROTECTED]] Sent: Monday, January 14, 2002 3:52 PM To: '[EMAIL PROTECTED]' Subject: FW: SQL/Design -- how to use sub entities/type discriminators ? You don't really have to do the two searches and if-then-else. You can use joins to first get all grads, then a join to get all profs. SELECT user.*,graduser.* FROM graduser LEFT JOIN user USING(userid); SELECT user.*,profuser.* FROM profuser LEFT JOIN user USING(userid); These assume USERIDs are unique among grads and profs magic words: mysql, database -----Original Message----- From: j.random.programmer [mailto:[EMAIL PROTECTED]] Sent: Monday, January 14, 2002 2:56 PM To: [EMAIL PROTECTED] Subject: SQL/Design -- how to use sub entities/type discriminators ? 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, [EMAIL PROTECTED] __________________________________________________ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
