SQL - Combining two queries into one query-CodePudding

I have 2 queries.

For table1,

SELECT codesTable.code_id,COUNT(*) FROM table1,codesTable
WHERE table1.codeid=CodeTable.code_id 
AND table1.type_no IN (1,2,3,4,5,6,7,8,9,10) 
AND table1.recoverd_value IN (0,1) 
GROUP BY table1.code_id

For Table2

SELECT codesTable.code_id,COUNT(*) FROM table2,codesTable
WHERE table2.codeid=CodeTable.code_id 
AND table2.type_no IN (1,2,3,4,5,6,7,8,9,10) 
AND table2.recoverd_value IN (0,1) 
GROUP BY table2.code_id

Need to combine this as one query.

Note:

Result for 1st query: code_id and count of Table1 and

Result for 2nd query: code_id, count of table2.

Current output like code_id,count of table(1,2)

CodePudding user response：

SELECT code_id, SUM(counted)
FROM (
SELECT code_id,COUNT(*) AS counted
FROM table1,codesTable
WHERE table1.codeid=CodeTable.code_id 
AND table1.type_no IN (1,2,3,4,5,6,7,8,9,10) 
AND table1.recoverd_value IN (0,1) 
GROUP BY table1.code_id

UNION ALL

SELECT code_id,COUNT(*) AS counted
FROM table2,codesTable
WHERE table2.codeid=CodeTable.code_id 
AND table2.type_no IN (1,2,3,4,5,6,7,8,9,10) 
AND table2.recoverd_value IN (0,1) 
GROUP BY table2.code_id
) AS subq
GROUP BY code_id
;

Don't forget to use UNION ALL instead of UNION because UNION ALL saves duplicates from both tables.

CodePudding user response：

You can just use union for this operation where two tables will be combined excluding the duplicates. Code is Shown below.

(SELECT code_id,COUNT(*) FROM table1,codesTable
WHERE table1.codeid=CodeTable.code_id 
AND table1.type_no IN (1,2,3,4,5,6,7,8,9,10) 
AND table1.recoverd_value IN (0,1) 
GROUP BY table1.code_id)
UNION
(SELECT code_id,COUNT(*) FROM table2,codesTable
WHERE table2.codeid=CodeTable.code_id 
AND table2.type_no IN (1,2,3,4,5,6,7,8,9,10) 
AND table2.recoverd_value IN (0,1) 
GROUP BY table2.code_id)

Hope this clarified your doubt.

CodePudding user response：

You may union all the required results from table1 and table2 then join this unioned query to codesTable as the following:

SELECT D.codeid, COUNT(*) CN
FROM
(
  SELECT codeid, type_no, recoverd_value
  FROM table1
  UNION ALL
  SELECT codeid, type_no, recoverd_value
  FROM table2
) D
JOIN codesTable T
ON D.codeid = T.code_id
WHERE D.type_no IN (1,2,3,4,5,6,7,8,9,10) AND D.recoverd_value IN (0,1)
GROUP BY D.codeid
ORDER BY D.codeid

See a demo.