MySQL - selecting from multiple tables

Soldato
Joined
18 Oct 2003
Posts
19,413
Location
Midlands
Hi all,

My limited database knowledge is holding me back. I have the following psuedo SQL query that I know I should be able to JOIN or IN some way so I don't have to do multiple queries, but I just can't get it to work. This probably explains my situation easiest:

Code:
SELECT comment_ID, comment_post_ID, user_id, comment_id, meta_value FROM $commentstable, $commentmetatable WHERE user_id = $currentuserid AND comment_ID = comment_id


comment_ID is in $commentstable and comment_id is in $commentsmetatable and I know it's this column that links the two tables together, but not sure how to get the results I want.

Any help is much appreciated.
 
Soldato
OP
Joined
18 Oct 2003
Posts
19,413
Location
Midlands
SELECT a.*, b.*
FROM tableA a
LEFT JOIN tableB b
ON a.colA = b.colA

Change as appropriate
That helped, big time. Got my function up and running a charm. Thanks Billy too, I hadn't twigged the dot mechanism.

I did think about throwing a third table into the mix. What would be more efficient, combining the DB query to include a 3rd table and match on the comment_post_ID. Or loop the first array for my condition and perform X amount of more specific DB queries? I imagine doing it all with one query to get the data I need is better, but how would I go about joining another table. Can I just do something as follows:

SELECT a.*, b.*, c.*
FROM tableA a
LEFT JOIN tableB b
ON a.colA = b.colA
LEFT JOIN tableC c
ON b.colA = c.colA
WHERE conditions
 
Soldato
Joined
20 Oct 2008
Posts
12,096
You want to reduce the number of round trips to the database as far as you can.

You can join multiple tables as you suggest.

Depending on the result you want it isn't always going to be a LEFT JOIN.
 
Soldato
OP
Joined
18 Oct 2003
Posts
19,413
Location
Midlands
Cheers, read up more on the JOINs to get the right one and added in my third table. Had to use some AS commands too because two of my cols had the same name. Got it all working with 1 DB query, superb. Thanks all.
 
Back
Top Bottom