I have 2 tables - posts and users . I linked the id field in users with the autor_id field in posts . How can I get the author's name from users in the posts selection from posts ? For this purpose, join or union to use?

    2 answers 2

    If you use the framework, then the request for data will be approximately of the following form:

     SELECT posts.*, users.`name` as `user_name` FROM posts INNER JOIN users ON (posts.`autor_id`=users.`id`) WHERE posts.`id`=?

    If you need any more data from the users table, you need to add them to the select section and prescribe an alias, in case the field names in both tables match.

    • I do not use the framework, but your request works. Thank! - Vlad
    • And why do you need reverse quotes? - Vlad
    • @Vlad - reverse quotes mysql understands how the names of columns, tables, databases. Using them eliminates the possibility of an error, for example, because of the column name that matches the keyword, for example group - Reinq

    You can solve the problem in two ways, or by using the JOIN operator

     SELECT p.id AS id, p.content AS content, u.name AS name FROM posts AS p LEFT JOIN users AS u ON p.author_id = u.id

    Or you can use the subquery

     SELECT id, content, (SELECT name FROM users WHERE id = posts.author_id) FROM posts WHERE id = 145
    • Thank you very much! And why do you need connections in the tables if you can solve everything in the query? - Vlad
    • @Vlad, do you mean foreign keys like author_id or something else? - cheops
    • Well yes. I linked in phpmyadmin an id of one with author_id of another table. Why do you need to do this at the database level - if a query can get a selection of unrelated tables? Sorry for the dumb question - I didn't understand this moment)) - Vlad
    • one
      @Vlad, this is just very important, you cannot bind data in a normal way without such a connection. Note that in each request author_id is used (just in different places). Without it, you can not determine which user which post belongs to. You can make a cross-union, but each post will be associated with each post, but since there is no connection information, you will not do anything with such a table. Foreign key allows you to establish such a connection - cheops
    • @Vlad is a normal practice for maintaining the referential integrity of the database - Reinq