2 Attachment(s)
[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.
Attachment 159867
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
Re: how to Query Data from 2 tables and combine the results in one table
hi Sir ZVoni,
thanks for the code, I am new to this Sir, please advice , I tried your code but got an error.
command not properly ended at 'AS'
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
Re: how to Query Data from 2 tables and combine the results in one table
hi Sir,
I got it , thanks .