Question 21496 in Mysql Question posted in
The official MySQL documentation can be found here.

How to assign values to a sql query inside multiple where clause and run query in single command – Mysql

I have values array as :

const params = [
['2022-12-10', 'aaaaa', '2022-12-01', 'xhxha', '2022-12-10'],
['2022-12-11', 'ababa', '2022-12-01', 'xhxha', '2022-12-11'],
['2022-12-12', 'acaca', '2022-12-01', 'xhxha', '2022-12-12'],
['2022-12-13', 'adada', '2022-12-01', 'xhxha', '2022-12-13'],
];

const data = await db.query(`select id, title, DATE_FORMAT(end_date,"%Y-%m-%d") as end_date ABS(DATEDIFF(?, end_date))+1 as delay from chart 
where uid = ?
and date = ?
and project_uid = ?
and end_date = ?
and completed is true;
`, [params]);

I want to run this query, but all the values are being added to the 1st ? (question mark). I want to have the the values to different ? and get the result.

Also I do not want to run query inside for loop. How do I get the result here?

Tags:

2

Answers


  1. 0

    You can join to a parameters subquery. Since 8.0 you can use VALUES table constructor. Alternatively use SELECT .. UNION ALL in params subquery

    select id, title, DATE_FORMAT(end_date,"%Y-%m-%d") as end_date ABS(DATEDIFF(?, end_date))+1 as delay
from (
  values
   row(date '2022-12-10', 'aaaaa', date '2022-12-01', 'xhxha', '2022-12-10'),
   row(date '2022-12-11', 'ababa', date '2022-12-01', 'xhxha', '2022-12-11'),
   row(date '2022-12-12', 'acaca', date '2022-12-01', 'xhxha', '2022-12-12'),
   row(date '2022-12-13', 'adada', date '2022-12-01', 'xhxha', '2022-12-13')
) params(p1, p2, p3, p4 ,p5)
join chart c
on c.uid = params.p1
and c.date = params.p2
and c.project_uid = params.p3
and c.end_date = params.p4
and c.completed is true
    Login or Signup to reply.
  2. 0 
    SELECT id, title, DATE_FORMAT(end_date,"%Y-%m-%d") as end_date, ABS(DATEDIFF(?, end_date))+1 as delay
FROM (
  values
   row(date '2022-12-10', 'aaaaa', date '2022-12-01', 'xhxha', '2022-12-10'),
   row(date '2022-12-11', 'ababa', date '2022-12-01', 'xhxha', '2022-12-11'),
   row(date '2022-12-12', 'acaca', date '2022-12-01', 'xhxha', '2022-12-12'),
   row(date '2022-12-13', 'adada', date '2022-12-01', 'xhxha', '2022-12-13')
) params(param1, param2, param3, param4, param5)
JOIN CHART
on CHART.uid = params.param1
AND CHART.date = params.param2
AND CHART.project_uid = params.param3
AND CHART.end_date = params.param4
AND CHART.completed is true;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search