I am trying to create a subquery (DB Fiddle) to integrate into the main query.
There are two queries used as references to create the subquery.
The subquery requires producing the same output as creating a UNION of these two queries.
Query I
SELECT DISTINCT bk1.id FROM books bk1
INNER JOIN engages eg1 ON eg1.prid = bk1.prid
WHERE eg1.id = 2 AND eg1.typ = 1;
| id |
| --- |
| 1 |
| 2 |
| 3 |
---
Query II
SELECT DISTINCT bk1.id FROM books bk1
INNER JOIN promotes pm1 ON pm1.bkid = bk1.id
INNER JOIN engages eg1 ON eg1.prid = pm1.prid
WHERE eg1.id = 2 AND eg1.typ = 1
AND pm1.point = 5 AND bk1.prid <> 2;
| id |
| --- |
| 5 |
What I have tried so far:
books: id, prid, title, ...
engages: id, prid, typ, ...
promotes: bkid, prid, point, ...
---
SELECT DISTINCT bk1.id FROM books bk1
INNER JOIN engages eg1 ON eg1.prid = bk1.prid
INNER JOIN promotes pm1 ON pm1.point = 5
WHERE eg1.id = 2 AND eg1.typ = 1
OR (pm1.bkid = bk1.id AND bk1.prid <> 2);
| id |
| --- |
| 1 |
| 2 |
| 3 |
---
Desired output:
| id |
| --- |
| 1 |
| 2 |
| 3 |
| 5 |
---
Main Query:
SELECT bk.*, ab.col1, ab.col2, bc.col2, SUM (xyz), ...
FROM books bk
INNER JOIN ab_table ab ...
LEFT JOIN bc_table bc...
<OTHER JOINS>
INNER JOIN (SUBQUERY) sq ON sq.id = bk.id
WHERE <OTHER CONDITIONS>
GROUP BY bk.id ORDER BY bk.id DESC
LIMIT 50
Minimum reproducible code:
CREATE TABLE `books` (
`id` int(10) UNSIGNED NOT NULL,
`prid` int(10) UNSIGNED NOT NULL,
`title` varchar(100) NOT NULL
);
INSERT INTO `books` (`id`, `prid`, `title`) VALUES
(1, 1, 'Physics'),
(2, 1, 'Chemistry'),
(3, 1, 'Maths'),
(4, 2, 'Zoology'),
(5, 3, 'Biology');
CREATE TABLE `engages` (
`id` int(11) NOT NULL,
`prid` int(11) NOT NULL,
`typ` bit(1) NOT NULL DEFAULT b'1'
);
INSERT INTO `engages` (`id`, `prid`, `typ`) VALUES
(2, 1, b'1');
CREATE TABLE `promotes` (
`bkid` int(11) NOT NULL,
`prid` int(11) NOT NULL,
`point` tinyint(1) NOT NULL DEFAULT 5,
`enroll` bit(1) NOT NULL DEFAULT b'1'
);
INSERT INTO `promotes` (`bkid`, `prid`, `point`, `enroll`) VALUES
(1, 1, 0, b'1'),
(2, 1, 0, b'0'),
(3, 1, 0, b'1'),
(4, 1, 5, b'1'),
(4, 2, 0, b'1'),
(5, 1, 5, b'1'),
(5, 3, 0, b'0');
ALTER TABLE `books` ADD PRIMARY KEY (`id`);
ALTER TABLE `engages` ADD PRIMARY KEY (`id`,`prid`);
ALTER TABLE `promotes` ADD PRIMARY KEY (`bkid`,`prid`);
ALTER TABLE `books` MODIFY `id` int(10)
UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `books` ADD INDEX(`prid`);
ALTER TABLE `engages` ADD INDEX(`id`, `prid`);
ALTER TABLE `promotes` ADD INDEX(`prid`, `bkid`);
The books table has millions of rows.
The main query runs in chunks and executes in a fair minimal time.
What should be the correct (and performance-optimum) subquery to produce the desired output without the UNION of those two queries?
Update
prid is a foreign key that refers to the primary key of a table named person. The same is true for the id column in the engages table.