MySQL - selecting from multiple tables

[gord]

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:

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.

 

[GeordieStew]

SELECT a.*, b.*
FROM tableA a
LEFT JOIN tableB b
ON a.colA = b.colA

Change as appropriate

 

[Randomface74]

You can specify the table name with dot in between e.g. TableName.ColumnName

Or you can use aliases in place of the table names like the post above.

 

[gord]

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

 

[bremen1874]

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.

 

[gord]

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.