Page 1 of 1

SQL joins

Posted: Thu Nov 02, 2006 6:06 pm
by Dave2000
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


If it can be done, should i ALWAYS pull data using a join query? Such as the below, it could be done in one query using a join. It is always more efficient to do this?

Code: Select all

$result = mysql_query("SELECT username FROM users WHERE userid = '$userid'");
$row = mysql_fetch_assoc($result);
$username = $row['username'];
and...

Code: Select all

$result = mysql_query("SELECT new_messages FROM messages WHERE userid = '$userid'");
$row = mysql_fetch_assoc($result);
$new_messages = $row['new_messages'];
Thanks,

Shears


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Thu Nov 02, 2006 7:09 pm
by feyd
Which to use can depend greatly on what you're pulling. Two well built queries can perform similarly to a well written join. However a join that is too loose will cost you a lot of processing time on the database server.

Posted: Fri Nov 03, 2006 5:12 am
by Dave2000
Two well built queries
Unfortunately, I am too much of a n00b to know what a well built query is. I dont know of any other way of pulling a single piece of data from a table - but maybe there is :?

Could you advise my please. In this case of mine...would you advise me to use two queries or one join? (...or do you need to know more information to give an answer, though i cant think what).

Thank you,

Shears

Posted: Fri Nov 03, 2006 6:20 am
by theFool
Maybe u just try something like this:

Code: Select all

SELECT users.username, messages.new_messages FROM users INNER/RIGHT JOIN messages 
ON users.userid = messages.userid
WHERE users.userid = '$userid'
just modify the join and see if it runs smoothly.

now go and meditate about this young grasshopper :D

Posted: Fri Nov 03, 2006 9:07 am
by Dave2000
Thanks for not answering the question! I am not asking how to do a join query. I want to know which is more efficient. I have a few (four) tables that i wish to pull information from (two or three fields in each table) for a single row. I could use 4 simple queries, one for each table. However, would it be more efficient to combine the four queries into one join query. I know as a general rule, the fewer queries the better, but i don't know whether this should always be done... :?

Thank you

Shears

Posted: Fri Nov 03, 2006 9:51 am
by theFool
Mhh... yeah Shears maybe you're right. I thought u need help with the building of a join query to try it out.

But if you really want to know i only can agree to feyd and say "it depends".
See, if you want some values from a table and have to go over n (2..3..4) tables for that... a join might give u some nitro for that. If you want values from all tables (like in your example above) the speed might be nearly similar as when u make 2 queries.

The only hint i can give u is therefore again: Build both kinds of querys, try them out and see what is more convenient for you.


But if u want a definitve, simple answer to be satisfied with: use a join query.

Posted: Wed Nov 15, 2006 4:42 pm
by Boxers
In your example I don't think it matters a great deal from a performance point of view. Personally I would only use a join if the query actually needs it. In you case you don't need a join becuase you can pull the data back in two separate queries. In fact, by using a join you are actually pulling back more information than you need to (i.e. the username multiple times for each message)

Now, lets say you only knew the username and you wanted all the messages, then you HAVE to use a join because username doesn't exist on the messages table, so you need to join on user id in order to fetch all the messages for the user name.

Not sure if that helps, but just sharing my thoughts.

Posted: Thu Nov 16, 2006 9:34 pm
by fractalvibes
FWIW, I always try to get everything I need in one query and join what I need to join to get the results
needed. Less network traffic. Let the database do the work and do what it is designed to do.
And quite often, the specific join criteria values would be impossible to know in advance - you just need to know they have to be equal across different tables.

fv