Is it possible to add multiple on clauses inside an INNER JOIN clause?-CodePudding

I have a query that works perfectly, however I need to change it a bit but it shows me an error and I can't figure out why. Below is the code before and after the changes I made:

BEFORE:

SELECT *,
  (SELECT GROUP_CONCAT(pho_file_name) FROM post_images WHERE pho_post_id=posts.ID) AS photo_file_array
  FROM users
  INNER JOIN posts ON users.Id = posts.post_author
  ORDER BY posts.ID;

AFTER:

SELECT *,
  (SELECT GROUP_CONCAT(pho_file_name) FROM post_images WHERE pho_post_id=posts.ID) AS photo_file_array
  FROM users WHERE users.Id = "1"
  INNER JOIN posts ON users.Id = posts.post_author ON posts.post_date = "2020-12-04 07:51:21"
  ORDER BY posts.ID;

It shows me the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INNER JOIN posts ON users.Id = posts.post_author AND posts.post_date "2020-12...' at line 4

I'm a newbie on MySql but from what I can understand I think the error occurs because of the the double ON inside the INNER JOIN. So, is it possible to add multiple ON inside the INNER JOIN? Thanks in advance!!

CodePudding user response：

You have a few syntax issues, you can't put joins and where anywhere, you also need to use the correct delimiters and data types.

Try the following and note using table and column aliases makes for an easier-to-read query.

Additionally, consider not using select * and reference only the columns you actually require, if possible.

SELECT u.*, p.*, (
  SELECT GROUP_CONCAT(i.pho_file_name) 
  FROM post_images i 
  WHERE i.pho_post_id = p.ID
) AS photo_file_array
FROM users u
JOIN posts p ON p.post_author = u.Id 
            AND p.post_date = '2020-12-04 07:51:21'
WHERE u.Id = 1
ORDER BY p.ID;

CodePudding user response：

Here is a full working query. The errors (double ONclause, WHERE clause in the wrong position, wrong quotes) are corrected. Moreover, the ID is compared to an integer now and the post_date to a timestamp literal. I've used table aliases to get this more readable.

SELECT
  u.*,
  p.*,
  (
    SELECT GROUP_CONCAT(pi.pho_file_name) 
    FROM post_images pi
    WHERE pi.pho_post_id = p.id
  ) AS photo_file_array
FROM users u
INNER JOIN posts p ON p.post_author = u.id
                  AND p.post_date = TIMESTAMP '2020-12-04 07:51:21'
WHERE u.id = 1
ORDER BY p.id;

As to the tables: I suggest you are more consistent with your column names. Why do you call the post ID post_author? One would assume a name here. Just call it post_id in every table. And you don't have to precede columns with abreviations like pho. Just qualify all columns with their tables like I did in my query.