This is mainly a SQL syntax question but I am using the SQL for creating views in SQLite database files.
Basic question: What is the proper SQL syntax for "Left Outer Joins" for 1 primary with N secondary (support) tables; I have come up with 2 possibilities that work but not sure which one is better or more efficient. It is best to explain with an actual example from the system under development: Primary Table: UserTable - UserNumber (Primary Key) - UserTypeKey (Foreign Key) - UserName - UserStatusKey (Foreign Key) - Password - PasswordHint Secondary Tables: UserTypeTable - UserTypeKey (Primary Key) - UserType UserStatusTable - UserStatusKey (Primary Key) - UserStatus You can probably guess where I am going next, but I am creating a "UserView" where the "UserType" and "UserStatus" text fields are "joined" from the respective tables, the foreign keys in the UserTable could be NULL hence the need for "Left Outer Joins". Version #1: CREATE VIEW UserView AS SELECT UserNumber, UserType, UserName, UserStatus, Password, PasswordHint FROM (SELECT UserNumber, UserType, UserName, UserStatusKey, Password, PasswordHint FROM UserTable LEFT JOIN UserTypeTable USING(UserTypeKey)) LEFT JOIN UserStatusTable USING(UserStatusKey) Version #2: CREATE VIEW UserView AS SELECT UserNumber, UserType, UserName, UserStatus, Password, PasswordHint FROM UserTable LEFT JOIN UserTypeTable ON UserTable.UserTypeKey=UserTypeTable.UserTypeKey LEFT JOIN UserStatusTable ON UserTable.UserStatusKey=UserStatusTable.UserStatusKey Questions: - How would you do what I am trying to do? - Are these SELECT queries equivalent? Probably not as version 1 has a sub-select query so this is a "2 pass" query, but is version 1 done in "1 pass" or "2 passes"? - Is version 2 more efficient? - For some reason, in version 2 I could not use the "USING" shortcut when the joined fields had the same name so had to explicitly use the "ON" as shown above, anybody know why? Any feedback/help is much appreciated - thanks, Scott McDonald ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
