I have following tables:
TABLE_A
| some_id | entity_id| tableB_id| position |
|-------------------|----------|----------|----------|
| 388 | 8 | 5783 | 0 |
| 389 | 8 | 776 | 1 |
| 390 | 8 | 5765 | 2 |
| 391 | 8 | 5743 | 3 |
| 392 | 8 | 6167 | 4 |
| 393 | 8 | 6168 | 5 |
| 394 | 8 | 6169 | 6 |
| 708 | 11 | 501 | 3 |
TABLE_B
| some_id | key_1 | key2 | tableC_id |isSomeCheck|isOtherCheck|
|-------------------|---------|-------------- |-----------|-----------|------------|
| 776 | null | pqrstuvw1234| 311 | 1 | 0 |
| 5743 | sfrwt0 | sofdsfsdr456| 1829 | 1 | 0 |
| 5765 | null | abcdefgh4567| 321 | 1 | 0 |
| 5783 | null | something341| 1841 | 1 | 0 |
| 6167 | somer9 | null | 1956 | 0 | 0 |
| 6168 | richt6 | null | 1957 | 0 | 0 |
| 6169 | done60 | null | 1957 | 0 | 0 |
TABLE_C
| some_id | key_1 | sortName |
|-------------------|----------|----------|
| 311 | tes456 | WWW |
| 321 | don890 | UNK |
| 1829 | som123 | ILA |
| 1841 | ken980 | ARI |
| 1956 | lam123 | KOR |
| 1957 | kes648 | ERO |
Either isSomeCheck or isOtherCheck must be 1.
Current query that I am running returns results where only key_1 in TABLE_B is present:
SELECT TABLE_A.some_id FROM TABLE_B
JOIN TABLE_A
ON TABLE_A.tableB_id = TABLE_B.some_id
JOIN (SELECT tableB.some_id AS tableBID,
tableC.sortName AS tableCSortName
FROM TABLE_B AS tableB
INNER JOIN TABLE_C AS tableC
ON tableB.tableC_id = tableC.some_id
GROUP BY tableB.some_id)
ON TABLE_A.tableB_id = tableBID WHERE TABLE_B.key_1 in (select distinct(key_1) from TABLE_B where key_1 = TABLE_B.key_1 and TABLE_B.isRequired = 1 or TABLE_B.isChecked = 1) AND entity_id = 8 ORDER BY TABLE_A.position
Outputs:
| some_id |
|-------------------|
| 391 |
Everything works fine, except I also want to have some_id from TABLE_A if key_1 is absent from TABLE_B and key_2 is present like an OR condition. Basically, I also want to consider key_2 in the output.
So, my expected output would look like:
| some_id |
|-------------------|
| 388 |
| 389 |
| 390 |
| 391 |
What I tried and returns correct output:
SELECT TABLE_A.some_id FROM TABLE_B
JOIN TABLE_A
ON TABLE_A.tableB_id = TABLE_B.some_id
JOIN (SELECT tableB.some_id AS tableBID,
tableC.sortName AS tableCSortName
FROM TABLE_B AS tableB
INNER JOIN TABLE_C AS tableC
ON tableB.tableC_id = tableC.some_id
GROUP BY tableB.some_id)
ON TABLE_A.tableB_id = tableBID
WHERE
(TABLE_B.key_2 in (select distinct(key_2) from TABLE_B where key_2 = TABLE_B.key_2 and TABLE_B.isSomeCheck = 1 or TABLE_B.isOtherCheck = 1) or
TABLE_B.key_1 in (select distinct(key_1) from TABLE_B where key_1 = TABLE_B.key_1 and TABLE_B.isSomeCheck = 1 or TABLE_B.isOtherCheck = 1))
AND entity_id = 8 ORDER BY TABLE_A.position;
Is this the right way of achieving the output I need or is there any better way to achieve this so that I can update WHERE clause in the above query?
I would have liked to remove the duplicated code that checks isSomeCheck and isOtherCheck in both of the select distinct query. Thanks!
You don't need to use a sub-query to check the values of TABLE_B columns, you already joined this table and you could access its columns directly, so your first query could be written as the following:
SELECT tbl_a.some_id
FROM TABLE_B tbl_B JOIN TABLE_A tbl_a
ON tbl_a.tableB_id = tbl_b.some_id
JOIN TABLE_C tbl_c
ON tbl_b.tableC_id = tbl_c.some_id
WHERE tbl_a.entity_id = 8 AND
(tbl_b.isSomeCheck = 1 OR tbl_b.isOtherCheck = 1) AND
tbl_b.key_1 IS NOT NULL
ORDER BY tbl_a.position
The use of tbl_b.key_1 IS NOT NULL condition is to check the presence of key_1 in table_b, here we could use the coalesce function to check the presence of key_1 or key_2 in table_b. The coalesce function will return the first not null value, so your second query could be written as the following:
SELECT tbl_a.some_id
FROM TABLE_B tbl_B JOIN TABLE_A tbl_a
ON tbl_a.tableB_id = tbl_b.some_id
JOIN TABLE_C tbl_c
ON tbl_b.tableC_id = tbl_c.some_id
WHERE tbl_a.entity_id = 8 AND
(tbl_b.isSomeCheck = 1 OR tbl_b.isOtherCheck = 1) AND
COALESCE(tbl_b.key_1, tbl_b.key2) IS NOT NULL
ORDER BY tbl_a.position