Is it possible to join multiple querys in one?
I have 2:
SELECT DATEDIFF(CURRENT_DATE, cl.updated_at) AS Diff
FROM Table
WHERE Color = 'Red'
and
SELECT DATEDIFF(CURRENT_DATE, cl.updated_at) AS Diff
FROM Table
WHERE Color = 'Blue'
Now i want both in one query
So is try like this:
SELECT
(
SELECT DATEDIFF(CURRENT_DATE, cl.updated_at) AS Diff
FROM Table
WHERE Color = 'Red'
)
(
SELECT DATEDIFF(CURRENT_DATE, cl.updated_at) AS Diff2
FROM Table
WHERE Color = 'Blue'
)
But doenst work :(
You could use a single query with IN operator. This is a short for multiple OR conditions:
SELECT DATEDIFF(CURRENT_DATE, cl.updated_at) AS Diff
FROM Table
WHERE Color IN ( 'Red', 'Blue' )
If you really insist on (discouraged) having two queries, then use UNION ALL to combine them:
SELECT DATEDIFF(CURRENT_DATE, cl.updated_at) AS Diff
FROM Table
WHERE Color = 'Red'
UNION ALL -- does not remove duplicates from output
SELECT DATEDIFF(CURRENT_DATE, cl.updated_at) AS Diff
FROM Table
WHERE Color = 'Blue'
If you want to remove duplicates use UNION instead of UNION ALL.
If you need result in different columns as mentioned in a comment then use a CASE statement for that. I really don't see a reason for this though.
SELECT
CASE WHEN Color = 'Red' THEN DATEDIFF(CURRENT_DATE, cl.updated_at) END AS Diff,
CASE WHEN Color = 'Blue' THEN DATEDIFF(CURRENT_DATE, cl.updated_at) END AS Diff2,
FROM Table
WHERE Color IN ( 'Red', 'Blue' )
And for second approach:
SELECT
CASE WHEN Color = 'Red' THEN Diff END AS Diff,
CASE WHEN Color = 'Blue' THEN Diff END AS Diff2
FROM (
SELECT Color, DATEDIFF(CURRENT_DATE, cl.updated_at) AS Diff
FROM Table
WHERE Color = 'Red'
UNION ALL -- does not remove duplicates from output
SELECT Color, DATEDIFF(CURRENT_DATE, cl.updated_at) AS Diff
FROM Table
WHERE Color = 'Blue'
) t
Color to columns in SELECT, so we know which difference relates to which color.
Isn't it just updating the where in this situation?
SELECT DATEDIFF(CURRENT_DATE, cl.updated_at) AS Diff FROM Table WHERE Color = 'Blue' OR Color = 'red'
And yes you can use an INNER JOIN or UNION, but for this example, that wouldn't be useful.
You can use the UNION operator to Join your two queries. https://www.w3schools.com/sql/sql_union.asp
If your output column is same in all your query then you can use UNION / UNION ALL like below:
SELECT DATEDIFF(CURRENT_DATE, cl.updated_at) AS Diff
FROM Table
WHERE Color = 'Red'
UNION
SELECT DATEDIFF(CURRENT_DATE, cl.updated_at) AS Diff
FROM Table
WHERE Color = 'Blue'
If you can live with the values in different rows, then just do:
SELECT Color, DATEDIFF(CURRENT_DATE, cl.updated_at) AS Diff
FROM Table
WHERE Color IN ('Red', 'Blue');
I added Color to the SELECT so you can distinguish the rows.
If the subqueries return one value, then you are simply missing a comma:
SELECT (SELECT DATEDIFF(CURRENT_DATE, cl.updated_at) AS Diff
FROM Table
WHERE Color = 'Red'
),
(SELECT DATEDIFF(CURRENT_DATE, cl.updated_at) AS Diff2
FROM Table
WHERE Color = 'Blue'
);
If the subqueries return multiple rows, then you need to explain the result set you want.
EDIT:
If you want the values to line up in two different columns, then you can to do something like this:
select max(diff_red) as diff_red, max(diff_blue) as diff_blue
from ((select (@rnr := @rnr + 1) as rn,
DATEDIFF(CURRENT_DATE, cl.updated_at) AS Diff_red,
NULL AS Diff_blue
from t cross join
(select @rnr := 0) params
where color = 'Red'
) union all
(select (@rnb := @rnb + 1) as rn,
NULL AS Diff_red,
DATEDIFF(CURRENT_DATE, cl.updated_at) AS Diff_blue
from t cross join
(select @rnrb:= 0) params
where color = 'Blue'
)
) rb
group by rn;
