I hate joining, any suggestions

[ohmusama]

I'm tired of joining tables together to get results that I want.

I've been looking into foreign key, constraint, and references, but they dont seem to work the way I want them too (of course not)...

Here's what I'm looking for,

Having a master user table, with say, user_id primary auto_increment etc,
and having then sub tables that reference back to the main user_id. So, log_login would link back to user_id, personal_info would link back to user_id, log_transactions would link back to a user_id, etc.

Then I would like to query the master table, for anything in the child tables, such as
SELECT firstname FROM master WHERE user_id=1 (or some variation of this).
(firstname is not in master, but in personal_info)

I hope this all makes sense.


Maybe its too much to ask, ...

But is there a way to use UPDATE/DELETE in this way as well?
ie
UPDATE master SET firstname='newname' WHERE user_id='1'
DELETE FROM master WHERE user_id='1' (this would delete all child tables, associated with the master)

I suppose my alternative is:
SELECT master.user_id, firstname, email, log_login.timestamp FROM master, personal_info, log_login, WHERE master.user_id=personal_info.user_id=log_login.user_id;

Until I get 50 tables like in my current db, then the FROM/WHERE clause becomes HUGE, ....

Maybe I'm looking for nested tables, I'm totally up for new ideas/opinions on this.

[chazzy]

what you're describing sounds correct if what you need is all of the data from every table.

i think most people would be satisfied with a couple at a time (for instance, the log information should only be available to administrators, maybe?)

[ohmusama]

:| Well. Should I just make a php wrapper to turn my Queries into multi table SELECT/DELETE/UPDATE?

ie.

function convert_select($query, array $custom_tables=null) {
//example query: $query = "SELECT `username` FROM `master` WHERE `user_id`='1'";

$tables = array('personal_info, log_login, log_transaction');
if(isset($custom_tables)) {
$tables = $custom_tables;
}

foreach($tables as $table) { $tables_from .= ",`$table`"; }
$query = str_replace('FROM `master`', "FROM `master`$tables_from", $query);

foreach($tables as $table) { $tables_where .= "`$table`.`user_id`="; }
$query = str_replace('WHERE `user_id`=', "WHERE $tables_where", $query);

//example query is now: $query = "SELECT `username` FROM `master`,`personal_info`,`log_login`,`log_transaction` WHERE `personal_info`.`user_id`=`log_login`.`user_id`=`log_transaction`.`user_id`='1';
return $query;
}