Subject Re: [IBO] KeyLinks, Join Links... Am I doing this right?
Author Helen Borrie
At 06:38 PM 19-02-01 -0800, you wrote:
>Here's my scenario. I have a users table and a sessions table. The
>sessions table will be frequently updated as users log in and out. I want
>to display a grid of which users are currently logged in.
>Table 1 - Sessions
> Session_ID (PK)
> User_ID (FK -> Users.User_ID)
> Location
> Start_Date
>Table 1 - Users
> User_ID (PK)
> Login_Name
> Login_Password
>SQL - select Sessions.*, Users.Login_Name from Sessions, Users where
>Users.User_ID = Sessions.User_ID;
> Sessions.Session_ID
> Users.User_ID = Sessions.User_ID
>This setup works, but I'm not sure if this is as it should be. Thank you
>for any input you have.


Your KeyLinks are dead suspect. The master table here is Users, not Sessions and the M-D relationship is Sessions for Users, not Users for Sessions, i.e. you potentially have many Sessions for one user, so Sessions.SessionID won't uniquely identify a row in your dataset.

I think if you clean up your statement, you might see a clearer way to that unique KeyLinks structure. To start with, get rid of SELECT * - it's not useful at the best of times and less than useless in a join.

Second, don't use the SQL-89 join syntax. The explicit join is a lot clearer and you can thereby avoid the parsing overhead of JoinLinks.

Try this:

select Users.Login_Name,
Sessions.Session_ID, (PK)
from Users
join Sessions
on Users.User_ID = Sessions.User_ID

Your Keylinks here would be Users.User_ID, and Sessions.Session_ID. No JoinLinks are required.


All for Open and Open for All
InterBase Developer Initiative ยท