1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL - selecting from multiple tables

Discussion in 'HTML, Graphics & Programming' started by gord, Aug 17, 2018.

  1. gord

    Capodecina

    Joined: Oct 18, 2003

    Posts: 19,036

    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.
     
  2. GeordieStew

    Associate

    Joined: Nov 3, 2014

    Posts: 39

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

    Change as appropriate
     
  3. billysielu

    Sgarrista

    Joined: Aug 9, 2009

    Posts: 9,684

    Location: Oxfordshire

    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.
     
  4. gord

    Capodecina

    Joined: Oct 18, 2003

    Posts: 19,036

    Location: Midlands

    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
     
  5. bremen1874

    Sgarrista

    Joined: Oct 20, 2008

    Posts: 8,699

    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.
     
  6. gord

    Capodecina

    Joined: Oct 18, 2003

    Posts: 19,036

    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.