[RESOLVED] how to Query Data from 2 tables and combine the results in one table
I want to query data from 1 table and other table then combine the result in one table.
here is my sql query code select statements.
Code:
----3412 STATION IRC4
SELECT LINE_NO,COUNT(MO_SN)AS IRC4_OUTPUT FROM DQC3412 WHERE MO_SN LIKE 'S46M%'AND STATION ='RC4' AND LINE_NO='H5' AND TEST_DTTM BETWEEN TO_DATE('20180530','yyyymmdd')
AND TO_DATE('20180530 235959','yyyymmdd hh24miss')
GROUP BY LINE_NO,STATION
----3422 STATION RC4
SELECT LINE_NO,COUNT(MO_SN)AS RC4_OUTPUT FROM DQC3422 WHERE MO_SN LIKE 'S46M5%'AND DEF_ITEM='OK' AND STATION ='RC4'AND LINE_NO='H5' AND TEST_DTTM BETWEEN TO_DATE('20180530','yyyymmdd')
AND TO_DATE('20180530 235959','yyyymmdd hh24miss')
GROUP BY LINE_NO
----3422 STATION VMI2
SELECT LINE_NO,COUNT(MO_SN)AS VMI2_OUTPUT FROM DQC3422 WHERE MO_SN LIKE 'S46M5%' AND DEF_ITEM='OK' AND STATION ='VMI2' AND LINE_NO='H5' AND TEST_DTTM BETWEEN TO_DATE('20180530','yyyymmdd')
AND TO_DATE('20180530 235959','yyyymmdd hh24miss')
GROUP BY LINE_NO
below is the output , I want to combine all in 1 table please help me. thanks in advance.
Re: how to Query Data from 2 tables and combine the results in one table
Not tested
SQL Code:
SELECT
T1.LINE_NO,
COUNT(T1.MO_SN) AS IRC4_OUTPUT,
COUNT(T2.MO_SN) AS RC4_OUTPUT,
COUNT(T3.MO_SN) AS VMI2_OUTPUT
FROM
DQC3412 AS T1
INNER JOIN
DQC3422 AS T2
ON
T1.STATION=T2.STATION AND
T1.LINE_NO=T2.LINE_NO AND
T1.TEST_DTTM=T2.TEST_DTTM AND
T2.DEF_ITEM='OK' AND
T2.MO_SN LIKE 'S46M5%'
INNER JOIN
DQC3422 AS T3
ON
T3.STATION='VMI2' AND
T3.LINE_NO=T1.LINE_NO AND
T3.TEST_DTTM=T1.TEST_DTTM AND
T3.DEF_ITEM='OK' AND
T3.MO_SN LIKE 'S46M5%'
WHERE
T1.MO_SN LIKE 'S46M%' AND
T1.STATION='RC4' AND
T1.LINE_NO='H5' AND
T1.TEST_DTTM BETWEEN TO_DATE('20180530','yyyymmdd') AND TO_DATE('20180530 235959','yyyymmdd hh24miss')
GROUP BY T1.LINE_NO
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
Re: how to Query Data from 2 tables and combine the results in one table
Remove the "AS" in all FROM-instances. I don't know which DBMS you're using
FROM DQC3412 T1
FROM DQC3422 T2
FROM DQC3422 T3
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
[RESOLVED] how to Query Data from 2 tables and combine the results in one table
Reply With Quote
