Scott McDonald wrote:
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?
Scott,
I prefer the second version myself. I think the intent is more obvious.
The following sqlite trace shows that the same query plan is used for
queries using either view. I believe they are equivalent.
I believe the error produced by the third view below is a bug in sqlite.
SQLite should still have access to the UserStatusKey field from both the
result of the first join and the USerStatusTable. The using clause in
the first join causes standard SQL engines to generate a single new
column that UserTypeKey, that is not considered to be a member of either
of the tables, and that replaces the UserTypeKey from both of the joined
tables.
SQLite version 3.5.4
Enter ".help" for instructions
sqlite> create table UserTable (
...> UserNumber integer primary key,
...> UserTypeKey integer referneces UserTypeTable,
...> UserName text,
...> UserStatusKey integer references UserStatusTable,
...> Password text,
...> PasswordHint text
...> );
sqlite>
sqlite> create table UserTypeTable (
...> UserTypeKey integer primary key,
...> UserType text
...> );
sqlite>
sqlite> create table UserStatusTable (
...> UserStatusKey integer primary key,
...> UserStatus text
...> );
sqlite>
sqlite>
sqlite> CREATE VIEW UserView1 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);
sqlite>
sqlite> CREATE VIEW UserView2 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;
sqlite>
sqlite> explain query plan select * from UserView1;
0|0|TABLE UserTable
1|1|TABLE UserTypeTable USING PRIMARY KEY
2|2|TABLE UserStatusTable USING PRIMARY KEY
sqlite>
sqlite> explain query plan select * from UserView2;
0|0|TABLE UserTable
1|1|TABLE UserTypeTable USING PRIMARY KEY
2|2|TABLE UserStatusTable USING PRIMARY KEY
sqlite>
sqlite> CREATE VIEW UserView3 AS
...> SELECT UserNumber, UserType, UserName, UserStatus, Password,
PasswordHint
...> FROM UserTable
...> LEFT JOIN UserTypeTable using(UserTypeKey)
...> LEFT JOIN UserStatusTable using(UserStatusKey);
SQL error: cannot join using column UserStatusKey - column not present
in both tables
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
