[SOLVED] to join or not to join?…

[jonniejoejonson]

Please can you help me by telling when I should and should not join 2 tables?…

Is it only when you want to compare things in 2 tables that you would use a join?… or would you also do it for the following problem…

 

For example if I had

 

Table 1.

A 'Comments' table… including a field ‘userId’… the Id of the user that leaves a comment

 

Table 2.

'ProfileDetails' table… including a field ‘userId‘ and ‘profileThumbnail’… (filename of profile picture.)

 

 

Now I need to get the correct profile pictures for the userId’s, of certain users that are in the comments table.

Do I somehow join the 2 tables…. Which I don’t see as necessary as I am not comparing any of the data?….

Or do I do 2 queries…

1. grab my userId’s from the commentsTable.

2. Grab the profileThumbnails from these grabbed userIds

 

I am self taught and am not really sure when to join tables and when just to do 2 queries… thanks to any responders.

 

[Mchl]

SELECT c.comment, c.userID, p.profileThumbnail FROM Comments AS c INNER JOIN ProfileDetails AS p USING (userID) WHERE c.commentID = 1

 

One query to give you a comment text, a user ID and a thumbnail for comment #1 in your table...

 

So yes: join.

[jonniejoejonson]

Thanks Mchl,

that works nicely...

I wish i understood it... but i will read up on joining tables, i really just needed to know if you would join the table instead of 2 queries... but you gonea step further, so thanks for your help.

[Mchl]

In most cases joining is preferable, as it will get you results quicker.

 

Joining can be tricky, but it's quite easy to grasp the basics.

 

In the example above, its just looking for a comment with commentID =1 in Comments table, and then tries to find matching userId in ProfileDetails table.

 

[jonniejoejonson]

thanks again mc...

can i ask one further question...

if in the comment table i had called the userId field something different eg ('commentUserId'), but it holds userId's, could i still join the table along this field...

if you understand what i mean? thanks again.

[Mchl]

You could, but the syntax would be slightly different:

 

SELECT c.comment, c.userID, p.profileThumbnail FROM Comments AS c INNER JOIN ProfileDetails AS p ON p.userID = c.commentUserId WHERE c.commentID = 1

[jonniejoejonson]

Thanks again Mchl,

Is there much difference between the amount of time it takes to run the first and second example?... i.e. should i change the field names so that i can use the first query example?, or it doesn't make any difference.

 

I think there is quite alot for my brain to get around on joining tables... anyway thanks again for your help, much appreciated.

[Mchl]

As far as I know, there will be no difference between the two. In fact, there's a part of mysql server called optimiser, that will parse and modify these statements to (usually) most optimal form.

[jonniejoejonson]

thanks Mchl, your help has been much appreciated... take care.