How to add to this query: the following fields:

  • name , surname from the datacenter table and
  • nane_avatar and has_avatar from the photo table.

in the existing request?

 mysql_query("SELECT sender_id, content FROM massage WHERE recipient_id = '$id'");
  • AlexWIndHope please go to VK - Zow
  • AlexLastHope, and then here - ling

2 answers 2

 SELECT message.sender_id, message.content, senderInfo.name, senderInfo.surname, photo.name_avatar, photo.has_avatar FROM massage LEFT JOIN datacenter AS senderInfo ON message.sender_id = senderInfo.id LEFT JOIN photo ON message.sender_id = photo.id WHERE message.recipient_id = '$id'

    The expression "A LEFT JOIN B" in MySQL is implemented as follows:

    • Table B is set to be dependent on table A and on all tables on which A. depends.
    • Table A is set to be dependent on all tables (except B) that are used in the LEFT JOIN clause.
    • All LEFT JOIN conditions are moved to the WHERE clause.
    • All standard join optimization methods are performed, except that the table is always read after all the tables on which it depends. If there is a circular dependency, MySQL will generate an error.
    • All standard WHERE optimization methods are executed.
    • If table A has a row that matches the WHERE clause, but table B doesn’t satisfy any LEFT JOIN condition, an additional row B is generated in which all column values ​​are set to NULL.
    • If LEFT JOIN is used to search for rows that are missing in some table, and the WHERE clause performs the following check: column_name IS NULL, where column_name is a column that is declared NOT NULL, MySQL will stop searching for rows (for a particular key combination) after that How to find a string that matches the LEFT JOIN clause.

    RIGHT JOIN is implemented in the same way as LEFT JOIN.

    If you specify a hard order for reading tables in LEFT JOIN and STRAIGHT JOIN, the link optimizer (which determines the order in which the tables should be linked) will do the job much faster, as it will need to check fewer table permutations.

    Note: it follows that if a query of type is executed

     SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key

    MySQL will do a full look at table b, because LEFT JOIN will force it to read this table before d.

    In this case, to prevent a full view of table b, you need to change the query in this way:

     SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key

    Taken with How MySQL Optimizes LEFT JOIN and RIGHT JOIN