Page 1 of 1

seeking join help

Posted: Sat Mar 20, 2004 1:39 pm
by m3rajk
i want to streamline the db. remove the extra refrences to usernames for the user id and use joins to get the name instead. i figure this brings more functionality and a smaller database.
example:

tops table has username, score, gender, user id
users table has much more, but also has gender and id and username


current querty looks a little like select username, gender, score from tops order by score desc
(uses user id to update)

to cross with what i know i'd need to change it to select user.username, users.gender, tops.score from tops where users.uid=tops/.uid order by score desc

i know there's inner left, outer left and outer right joins. i'm not familiar witht hem. i knwo some create new tables. i don't want that. i want to get it to look something like select username, gender, score from tops [some kinda] join tops.uid on users.uid order by score dec


i find the mysql online manual and all the manuals i've looked at confisuing on what kinda join i should do. i know this will allow me to just use the feild name.. so can anyone help me here?

thanx in advance, m3rajk

Posted: Sat Mar 20, 2004 3:39 pm
by qads
i have't read your post lol, but here is something that will save you time with joins etc :wink:

http://crlab.com/mybuilder/download.html

Posted: Sun Mar 21, 2004 2:16 pm
by m3rajk
only problem with that is i wanna actually learn sql, not some interface for it, that means i need to figure these out and that requires doing it directly. which means the headache and the questions, but also means i'm better with ti in the end.

Posted: Mon Mar 22, 2004 8:18 am
by Stryks
This is just from my experience trying to do the same thing, but I think you should really take the time to check out that SQL generator and the results you generate.

The best way to learn to my mind is to start out looking at the generated SQL code that does what you want and works correctly so that you can break it down and really figure out what it is doing.

Its the only way I could get my head around it ... most of the SQL manuals about the place make no real sense as nobody really seems inclined to tell you what the purpose of a command is, only how to implement them, which makes trying to understand how they are connected really difficult.

So, my advice is to get some sql working in the generator and find the commands and the sequence you will need. If you want to write it by hand and know all the parameters, thats great, but at least give yourself a hand by knowing what parameters you will need to learn.

Hope this helps

Posted: Mon Mar 22, 2004 9:20 am
by JAM
Perhaps:

Code: Select all

$sql = '
select 
    users.username, 
    users.gender, 
    tops.score 
from 
    users
    inner join tops on tops.id = users.id
order by 
    tops.score desc 
';
To cut i short, INNER joins need (in this case) the id we are joining with to be available in both tables.

I'm using an inner join here, as I imagine the users with no data in the tops table shouldn't be visible (edit to fit your needs).

LEFT/RIGHT needs the id in at least one of the tables (hence the name).

----------

Dunno if this helps, but...

Code: Select all

CREATE TABLE test (
  id tinyint(3) unsigned NOT NULL auto_increment,
  name varchar(10) default '0',
  PRIMARY KEY  (id)
) TYPE=MyISAM;

INSERT INTO test VALUES("1", "foo");
INSERT INTO test VALUES("2", "bar");
INSERT INTO test VALUES("3", "moo");

CREATE TABLE test2 (
  id tinyint(3) unsigned NOT NULL auto_increment,
  data varchar(10) default '0',
  PRIMARY KEY  (id)
) TYPE=MyISAM;
INSERT INTO test2 VALUES("1", "Testing");
INSERT INTO test2 VALUES("2", "something");
INSERT INTO test2 VALUES("3", "else");
INSERT INTO test2 VALUES("4", "here");
And here are the results...

Code: Select all

select * from test2
/* Result:
1,Testing
2,something
3,else
4,here
*/

Code: Select all

select test2.* from test inner join test2 on test.id = test2.id
/* Result:
1,Testing
2,something
3,else
*/

Code: Select all

select test2.* from test right join test2 on test.id = test2.id
/* Result:
1,Testing
2,something
3,else
4,here
*/

Code: Select all

select test2.* from test left join test2 on test.id = test2.id
/* Result:
1,Testing
2,something
3,else
*/

Posted: Wed Mar 24, 2004 7:32 pm
by m3rajk
Stryks wrote:This is just from my experience trying to do the same thing, but I think you should really take the time to check out that SQL generator and the results you generate.

The best way to learn to my mind is to start out looking at the generated SQL code that does what you want and works correctly so that you can break it down and really figure out what it is doing.

Its the only way I could get my head around it ... most of the SQL manuals about the place make no real sense as nobody really seems inclined to tell you what the purpose of a command is, only how to implement them, which makes trying to understand how they are connected really difficult.

So, my advice is to get some sql working in the generator and find the commands and the sequence you will need. If you want to write it by hand and know all the parameters, thats great, but at least give yourself a hand by knowing what parameters you will need to learn.

Hope this helps
last time i used a generator i found it used some quirks that made it really hard to follow the generated code and there were much simpler and faster ways to write what i wanted. once a friend showed it to me i understood everything. since then i've avoided generators like a plague and found that as long as i can get a grasp i learn it faster than friends.

Posted: Wed Mar 24, 2004 7:34 pm
by m3rajk
jam: does that use just the feild name?

i wish i had a way to ask mysql directly, but i have no support contract.