How can I refer to the columns of a table after I used INNER JOIN in MySQL-CodePudding

I asked this question once before but it got deleted because it was unnecessarily long, so I deleted it, edited it so that it would be shorter and to the point and reposted here:

I have the following tables in a MySQL database that are represented in the following image:

I want to get the fields id, id_user, body and post_date from all the posts with one or more specific tags that where originaly in the table posts. To achieve that I try to INNER JOIN posts to posts_has_tags to tags and get the posts.id, posts.id_user, posts.body, posts.post_date but I get the following error:

Unknown column 'posts.id' in 'field list'

The query I use to INNER JOIN is the following:

SELECT posts.id AS post_id, id_user, body , post_date
                  FROM posts INNNER JOIN posts_has_tags
                  ON post_id = posts_id
                  INNER JOIN tags
                  ON tags.id = tags_id

Why is posts.id raising an error? Is it beacause I have INNER JOINED the tables and if so what can I do to fix it?

P.S. I know that I didn't post the WHERE statement in the query but the problem is not there so I left it out.

CodePudding user response：

When you are dealing with multiple tables in a query preface each column references with the table that supplies that column. e.g.:

SELECT
    posts.id AS post_id ## nb this is a "column alias"
  , posts.id_user
  , posts.body
  , posts.post_date
FROM posts 
INNER JOIN posts_has_tags ON posts.id = posts_has_tags.posts_id
INNER JOIN tags           ON posts_has_tags.tags_id = tags.id

OR, to make this somewhat easier to prepare you can declare table aliases, then preface each column reference with the relevant alias instead, e.g.:

SELECT
    p.id AS post_id
  , p.id_user
  , p.body
  , p.post_date
FROM posts AS p  ## nb this is a "table alias"
INNER JOIN posts_has_tags AS pt ON p.id = pt.posts_id
INNER JOIN tags AS t            ON pt.tags_id = t.id

Whilst you can take the risk that a specific colum_name may be unique with a set of joined tables, this may not always be true if someone alters a table, so it is "good practice" (in my view) to always preface a column with its table or alias.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=223e1f48e64513a375146ca2e8a71343

Note: you alias posts.id as post_id, but that column name already exists in posts_has_tags. This "mixing-up" of columns names is a small example of why it is risky omit table/alias references when writing queries.

CodePudding user response：

You can’t join using an alias. Use posts.id in join and correct the typo INNNER JOIN and the query syntaxes.

SELECT posts.id AS post_id, id_user, body , post_date
FROM posts 
INNER JOIN posts_has_tags   ON posts.id = posts_id
INNER JOIN tags             ON tags.id = tags_id;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e0cba7e787fa1ab61a926a38b7e98e02