Help wih a query

[lurtnowski]

I have two tables

create table users (
   user_id SMALLINT NOT NULL AUTO_INCREMENT,
   name VARCHAR(50) NOT NULL,
   email VARCHAR(50) NOT NULL,   
   password VARCHAR(50) NOT NULL,
   created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   logged_in TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY ( user_id )
);

create table user_history (
   user_id SMALLINT NOT NULL,
   logged_in TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   logged_out TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   FOREIGN KEY ( user_id ) REFERENCES users (user_id)
);

Im trying to figure out how to make a slect query to pull data from both when you have a user_id
Heres what I have so far

SELECT users.name, user_history.logged_in, user_history.logged_out
FROM user_history
INNER JOIN users ON user_history.user_id =users.user_id;
``

[sibertius]

A complete jsfiddle with data makes it simpler to help you. http://sqlfiddle.com/#!9/824aee9/2

What is the desired result? Which database?

Have you tried with LEFT JOIN instead?

[igor_g]

1. Better if primary key is just “id”.

2. users LEFT JOIN user_history. You always have user, but not his history.

3. user_history.user_id should be unique. One history pro user. Create unique index for that.

4. Also primary key for user_history would be nice.

Oh… I’m sorry, I would to answer topic starter…

[r937]

@lurtnowski

Im trying to figure out how to make a slect query to pull data from both when you have a user_id

SELECT ...
  FROM ...  /* includes join */
 WHERE users.user_id = 'Todd'

[DaveMaxwell]

@igor_g

user_history.user_id should be unique. One history pro user. Create unique index for that.

That would be incorrect. He’s recording user sessions essentially, so there will be MANY per user. Otherwise, it wouldn’t make sense to have another table and he could just keep it on the users table.

[igor_g]

Hm…

Otherwise, it wouldn’t make sense to have another table and he could just keep it on the users table.

I think it could be. Users without login, some special users… And any way, if that is session - session is another entity and should have its own table.

But some other question: if this both tables related 1:n, there is should be completely different query. Or what exactly want author to get? Sessions list? Or users list wit their last sessions?

[sibertius]

@DaveMaxwell

That would be incorrect. He’s recording user sessions essentially, so there will be MANY per user. Otherwise, it wouldn’t make sense to have another table and he could just keep it on the users table.

In this case there are no need for unique id, but think it is a good habit to always have a unique id for every record regardless of purpose. So IMHO both unique id AND user_id as foreign key.

If you have a unique id, you can keep this id in memory and update logged_out using this id when logging out.

[DaveMaxwell]

@sibertius

In this case there are no need for unique id, but think it is a good habit to always have a unique id for every record regardless of purpose. So IMHO both unique id AND user_id as foreign key.

The only time it’s truly beneficial to have a unique id is if it’s referenced in another table, and that’s more just to eliminate the need to carry a multi-field FK on another table. So while I would probably put one on for the programming reasons you mentioned, it’s not required from a database perspective.

In this case, user_id and logged in WOULD create a unique key.

[r937]

@DaveMaxwell

The only time it’s truly beneficial to have a unique id is if it’s referenced in another table,

i agree, but i think what you meant to say is “surrogate key” – an artificial number, often an auto_increment or identity column, that acts as PK in place of other candidate keys (or when no candidate key exists, but in those cases i’d always go back to the ER design and redo it)

an actual unique ID is descriptive of every PK, and this includes composite PKs if you stretch the definition of “ID”

[m_hutley]

@DaveMaxwell

In this case, user_id and logged in WOULD create a unique key.

*theoretically.

(It is theoretically possible for 2 computers to simultaneously, or at least simultaneously to the degree of precision of TIMESTAMP, log in. The chances of collision are sufficiently slim as to be ignored, but it’s important to note that it is possible.)

[DaveMaxwell]

@r937

i agree, but i think what you meant to say is “surrogate key” – an artificial number, often an auto_increment or identity column, that acts as PK in place of other candidate keys (or when no candidate key exists, but in those cases i’d always go back to the ER design and redo it)

potato, tomato…semantics

I should have changed unique id to auto-increment. I’ve never heard the term surrogate key…learned something new…

@m_hutley

(It is theoretically possible for 2 computers to simultaneously, or at least simultaneously to the degree of precision of TIMESTAMP, log in. The chances of collision are sufficiently slim as to be ignored, but it’s important to note that it is possible.)

Yes, it’s theoretically possible that two computers for the same user could have the exact same login timestamp, but I would argue that the chances are so insignificant to render it nearly impossible. Time stamps are defined so granularly, the chances of this situation occurring are not worth considering.