seeking join help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

seeking join help

Post 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
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post 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
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post 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.
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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
*/
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post 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.
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post 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.
Post Reply