One to Many Join

[sniperscope]

Hello

Could someone tell me how can i merge tables has one-two-many relation. For example there is

 

user table:

CREATE TABLE IF NOT EXISTS `users` (

  `id` int(4) unsigned NOT NULL auto_increment,

  `userName` varchar(30) collate utf8_unicode_ci NOT NULL default '',

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

 

and some another table:

 

CREATE TABLE IF NOT EXISTS `login_table` (

  `id` int( unsigned NOT NULL auto_increment,

  `users_id` int(4) NOT NULL default '0',

  `login_date` int(10) NOT NULL default '0',

  `login_ip` int(20) NOT NULL default '0',

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4210 ;

 

I could not find a best solution so far.

 

What i want is get all users from users table and ip address from login _table ONLY logged in (let's say)today. Because users log everyday when they logged in.

 

Best Regard

[sniperscope]

Okay i just end up with a query something like

 

select users.id, login_table.login_date, login_table.login_ip  from users left join login_table on users.id = login_table.users_id AND login_table.login_date ='1316012400'

 

is this query fair enough for 20.000 record?

[awjudd]

You don't need the LEFT JOIN because it will just slow it down in this case (since you have a condition on the RH table anyways which will kill any NULLs).  I would put a FOREIGN KEY (use INNODB because MyISAM doesn't support FKs) constraint on the users_id column so that the optimizer is able to speed that up a bit.  But yes, it works.

 

That said, I personally would change the INT for the time to DATETIME.  You gain lots of additional functionality and readability of your queries for a few more bytes ...

 

~juddster

[sniperscope]

Thank you very much for your time and suggestion

I really appreciate that.

 

Regards.

Let me check what is Foreign key it is...

 

Have a nice day.

[awjudd]

Side Note: since login_date is an INT, you don't need to put the number in quotes.  You only need to do that for non-numeric data types.

 

~juddster

[fenway]

Side Note: since login_date is an INT, you don't need to put the number in quotes.  You only need to do that for non-numeric data types.

 

~juddster

Yes, but depending how you're handing injection vectors, the quotes rarely hurt.