select
problems.*,
case when coalesce(max(status_id), 2) = 2 then 'Not Completed' else 'Completed' end problem_status
from problems
left join tasks on tasks.problem_id = problems.id
group by problems.id, problems.title
order by problems.id;with problem_status as (
select
problems.*,
max(status_id) status_id
from problems
left join tasks on tasks.problem_id = problems.id
group by problems.id, problems.title
) select problem_status.id, problem_status.title, statuses.title
from problem_status
left join statuses on statuses.id = problem_status.status_id
order by problem_status.id;$sth = $pdo->prepare(
"SELECT 1
FROM `ok`
WHERE `val` = ?
LIMIT 1"
);
$sth->execute([$val]);
if ($sth->rowCount() > 0) {
printf("Value %s found in table `ok` column `val`", $val);
} else {
printf("Value %s not found in table `ok` column `val`", $val);
}WITH Ranked AS (
SELECT
OP.*,
DENSE_RANK() OVER (ORDER BY O.ID DESC) R
FROM Orders O
JOIN OrdersProducts OP ON O.Id = OP.OrderID
) SELECT * FROM Ranked
WHERE R <= 3;WITH Last3Orders AS (
SELECT TOP(3) Id FROM Orders O
ORDER BY Id DESC
) SELECT * FROM Last3Orders
JOIN OrdersProducts ON Last3Orders.Id = OrdersProducts.OrderID
ORDER BY OrderID DESC;create table user_balance (
user_id int,
balance decimal(9, 2) check (balance >=0)
);create table user_balance_unsigned (
user_id int,
balance decimal(9, 2) unsigned
);WITH RECURSIVE tmp AS (
SELECT REPORT_DATE rdate FROM ODK_INT_rep_p2 WHERE ODK_INT_rep_p2id IN (
SELECT nvl((SELECT ODK_INT_rep_p2id FROM ODK_INT_rep_p2 WHERE TYPE = 2 AND report_date = (SELECT report_date FROM ODK_INT_rep_p2 WHERE ODK_INT_rep_p2id = (SELECT max(ODK_REP_P2ID) FROM odk_svodka_skc2))),
(SELECT ODK_INT_rep_p2id FROM ODK_INT_rep_p2 WHERE TYPE = 2 AND report_date = (SELECT report_date
FROM ODK_INT_rep_p2 WHERE ODK_INT_rep_p2id = (SELECT max(ODK_INT_rep_p2id) AS ODK_INT_rep_p2id
FROM ODK_INT_rep_p2 WHERE TYPE = 1 AND trunc(report_date)= trunc(current_timestamp))))) AS id1
FROM
dual)),
WITH cte_months (day) AS (
...
)SELECT (
SELECT sum(wp_postmeta.meta_value) AS result
FROM wp_posts
JOIN wp_postmeta ON wp_postmeta.post_id = wp_posts.ID
WHERE wp_posts.post_status='publish' and wp_posts.post_type='zakazy-tsekha'
AND wp_postmeta.meta_key='summa_zakaza'
) - (
SELECT sum(wp_jet_cct_raskhody.tsena_raskhoda)
FROM wp_jet_cct_raskhody
) AS resultselect json_arrayagg(
json_object(
'id', id,
'headlineEn', headlineEn,
'posttextEn', posttextEn
)
)
from posts;-- Query 1: All records
SELECT
wp_posts.post_title,
CASE
WHEN v1.meta_key = 'id_sotrudnika' AND v2.meta_key = 'summa_poluchennoj_zarplaty'
THEN -v2.meta_value
ELSE v2.meta_value
END AS netto
FROM wp_posts
JOIN wp_postmeta v1 ON (wp_posts.ID = v1.post_id)
JOIN wp_postmeta v2 ON (wp_posts.ID = v2.post_id)
WHERE
v1.meta_key IN ('id_rab_1', 'id_sotrudnika') AND v1.meta_value = '981' AND
v2.meta_key IN ('summa_rab_1', 'summa_rab_2', 'summa_poluchennoj_zarplaty');
-- Query 2: Summarized records
SELECT
wp_posts.post_title,
SUM(CASE
WHEN v1.meta_key = 'id_sotrudnika' AND v2.meta_key = 'summa_poluchennoj_zarplaty'
THEN -v2.meta_value
ELSE v2.meta_value
END) AS netto
FROM wp_posts
JOIN wp_postmeta v1 ON (wp_posts.ID = v1.post_id)
JOIN wp_postmeta v2 ON (wp_posts.ID = v2.post_id)
WHERE
v1.meta_key IN ('id_rab_1', 'id_sotrudnika') AND v1.meta_value = '981' AND
v2.meta_key IN ('summa_rab_1', 'summa_rab_2', 'summa_poluchennoj_zarplaty');select d.DEPARTMENT_ID, d.DEPARTMENT_NAME, COUNT(e.EMPLOYEE_ID)
from departments d
left join employees e on d.DEPARTMENT_ID = e.DEPARTMENT_ID
group by d.DEPARTMENT_ID, d.DEPARTMENT_NAME;SELECT p.*, category_titles, filter_titles
FROM products as p
LEFT JOIN (
SELECT product_id, json_arrayagg(c.title) category_titles from product_category as pc
JOIN categories as c ON pc.category_id=c.id
GROUP BY product_id
) pc ON p.id=pc.product_id
JOIN (
SELECT product_id, json_arrayagg(f.title) filter_titles
FROM product_filter as pf
JOIN filters as f ON pf.filter_id=f.id
GROUP BY product_id
) pf ON p.id=pf.product_id
WHERE p.id='2'$q = mysqli_prepare($connection, "INSERT INTO `user_value` (`user_name`, `user_pass`) VALUES (?, ?)");
$q->bind_param("ss", $user_names, $user_pass);
$q->execute();SELECT "conversations".id
FROM "conversations"
JOIN "usersConversations" "uC1" ON "conversations".id = "uC1".conversation_id AND "uC1".user_id = 1
JOIN "usersConversations" "uC2" ON "conversations".id = "uC2".conversation_id AND "uC2".user_id = 2
WHERE "conversations"."isPrivate" = true
ORDER BY "conversations".id DESC
LIMIT 1;SELECT "conversations".id
FROM "conversations"
WHERE "conversations"."isPrivate" = true
AND EXISTS (SELECT 1 FROM "usersConversations" WHERE "conversations".id = "usersConversations".conversation_id AND "usersConversations".user_id = 1)
AND EXISTS (SELECT 1 FROM "usersConversations" WHERE "conversations".id = "usersConversations".conversation_id AND "usersConversations".user_id = 2)
ORDER BY "conversations".id DESC
LIMIT 1;select * from activity order by id asc limit 0, 3;
select * from activity order by id asc limit 3, 3;SELECT
p.*
FROM
products as p
WHERE
EXISTS (
SELECT 1 FROM product_filter pf WHERE pf.filter_id = 1 AND pf.product_id = p.id
)
AND EXISTS (
SELECT 1 FROM product_filter pf WHERE pf.filter_id = 2 AND pf.product_id = p.id
);with order_files as (
select
*,
row_number() over (partition by filename order by version desc, created_at desc) rn
from files
where user_id = 1
) select id, filename, user_id, version, created_at
from order_files where rn = 1
;SELECT *
FROM `articles`
LEFT JOIN `categories` ON articles.id_categorie = categories.id_categorie;SELECT *
FROM `articles`
LEFT JOIN `categories` USING (id_categorie);-- get records with wrong parents
select *
from test
where
parent_id is not null
and not exists (
select id from test as parents where parents.id = test.parent_id
);
-- delete records with wrong parents
delete test.*
from test
left join test as parents on parents.id = test.parent_id
where test.parent_id is not null and parents.id is null;
select * from test;
-- add foreign key to prevent missing parents
alter table test add foreign key (parent_id) references test(id);WITH d AS (
SELECT
*,
row_number() over (partition by key order by created desc) rn
FROM move_history
) SELECT *
FROM d
WHERE status = 'COMPLETED'
AND rn = 1
ORDER BY id;