questionon joins
i want to streamline my database and get rid of duplicate fields.
example: tops table is completely independant so that i can call it with one call and not have to use table.feild to get to data. this ends up doubling both the user id (the main way i synch data) and username (which is used to call the profiles)
in some scripts i call across tables by using selelct table.feild, table.field... where table.uid=table.uid and table.uid=table.uid and table.field=$user-given-data and table.field=$user-given-data and table.field=$user-given-data ...;
i want to move these to joins. i noticed there's two types. one creates a new table the other just acts like there's a new table and i can just call it by the feild name.
thing is i'm not too familliar with joins. what i'd like is pointers in making sure that i use the right kinda join to make it seem like there's a new one and just have to call by the feild name
do any of those with more expereince have any advice for me?
and can you verify this is the right format? (again, i'm new to using joins and want to verify i have the format right before implementing joins and streamlining the database)
inner join users.uid on tops.uid select feild,feild,... where feild=$argument
POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.
forwardtrends posted this at 02:18 — 19th March 2004.
He has: 52 posts
Joined: Feb 2003
This is the basic format I'd use.. if I'm right you don't need to create a new table with this join, you just need to pull info from two..
SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield
Is this what you needed?
Aaron Elliott
forwardtrends.com
m3rajk posted this at 19:59 — 19th March 2004.
They have: 461 posts
Joined: Jul 2003
yes! i beleive that's what i was seeking. the correct format. i've seen a variety of examples, all specifiying left and right inner and outer and it had me unsure of what was going on. i wanted to know that i can have an archetype (such as what you provided) that would work.
now i can stremaline the db and modify the scripts so everything is more efficient.. maybe give users the ability to change hteir usernames....
POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.
forwardtrends posted this at 20:25 — 19th March 2004.
He has: 52 posts
Joined: Feb 2003
yes you should be able to implement it easily.. an easy way of validating your SQL strings (at least what I do) - is connent to your database via Access (ODBC connections) - then you can use the query designer to visually create your query, convert to SQL mode and theres your code (basically)
Aaron Elliott
forwardtrends.com
m3rajk posted this at 23:34 — 19th March 2004.
They have: 461 posts
Joined: Jul 2003
only issue with that-- i'm using mysql and php
forwardtrends posted this at 23:45 — 19th March 2004.
He has: 52 posts
Joined: Feb 2003
oh.. yeah that might be tough lol
m3rajk posted this at 00:46 — 20th March 2004.
They have: 461 posts
Joined: Jul 2003
ok. here's an example. how i call the top users....
<?phpif($gen==='women'){
$selopts='<option value=\"all\">Men & Women</option><option value=\"women\" selected=\\"selected\\">Ladies</option><option value=\"men\">Men</option>';
$findtops=mysql_query(\"SELECT memname,score,gender FROM tops WHERE gender='F' ORDER BY score DESC\", $db); # get women by score
}elseif($gen==='men'){
$selopts='<option value=\"all\">Men & Women</option><option value=\"women\">Ladies</option><option value=\"men\" selected=\\"selected\\">Men</option>';
$findtops=mysql_query(\"SELECT memname,score,gender FROM tops WHERE gender='M' ORDER BY score DESC\", $db); # get men by score
}else{
$selopts='<option value=\"all\" selected=\\"selected\\">Men & Women</option><option value=\"women\">Ladies</option><option value=\"men\">Men</option>';
$findtops=mysql_query('SELECT memname,score,gender FROM tops ORDER BY score DESC', $db); # get people by joining date
}
?>
$findtops=mysql_query("SELECT username,score,gender FROM tops INNER JOIN users ON tops.memuid=users.uid WHERE gender='F' ORDER BY score DESC", $db);
or:
$findtops=mysql_query("SELECT username,score,gender FROM tops INNER JOIN users ON tops.memuid=users.uid WHERE gender='M' ORDER BY score DESC", $db);
or:
$findtops=mysql_query('SELECT username,score,gender FROM tops INNER JOIN users ON tops.memuid=users.uid ORDER BY score DESC', $db);
respectively. and then i can go and remove the memname feild from tops, thus eliminating a redundancy?
POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.
m3rajk posted this at 00:49 — 20th March 2004.
They have: 461 posts
Joined: Jul 2003
i wonder where mark is. the thread's been here for over 24 hours i think without a responsse from him. i've been to about a dozen sites. no one seems to be better than him with db answers. in the past the response time varied from me lucking out and being here when he's here to getting a response sometime the next day.
i've only seen one borads owner that's more involved than mark. it's a windows os issue board and the owner is there answering everything new every few hours (i swear he must have found a way to get paid for it). my guess is that whatever mark does for a job has him bogged down lately. i know i've ben working 50+ hour weeks due to people being on vacation lately. at least i'm hourly i guess...
POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.
Mark Hensler posted this at 01:38 — 20th March 2004.
He has: 4,048 posts
Joined: Aug 2000
Yes. If you move the username's to another table, you can remove the original field after you update all your SQL to use the new table.
Also note, if any two tables in a join query contain fields with the same name, you will need to use table.field syntax for that field.
m3rajk, I'll make a new thread about my situation. But basically, I've changed careers and I've lost a lot of free time.
Mark Hensler
If there is no answer on Google, then there is no question.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.