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?
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]
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.
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).
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...
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.
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.
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.