PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] how to Query Data from 2 tables and combine the results in one table-VBForums
Results 1 to 5 of 5

Thread: [RESOLVED] how to Query Data from 2 tables and combine the results in one table

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    90

    Resolved [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.
    Name:  eeeeeeeee.jpg
Views: 196
Size:  15.8 KB
    Attached Images Attached Images  

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,093

    Re: how to Query Data from 2 tables and combine the results in one table

    Not tested

    SQL Code:
    1. SELECT
    2. T1.LINE_NO,
    3. COUNT(T1.MO_SN) AS IRC4_OUTPUT,
    4. COUNT(T2.MO_SN) AS RC4_OUTPUT,
    5. COUNT(T3.MO_SN) AS VMI2_OUTPUT
    6.  
    7. FROM
    8. DQC3412 AS T1
    9.  
    10. INNER JOIN
    11. DQC3422 AS T2
    12. ON
    13. T1.STATION=T2.STATION AND
    14. T1.LINE_NO=T2.LINE_NO AND
    15. T1.TEST_DTTM=T2.TEST_DTTM AND
    16. T2.DEF_ITEM='OK' AND
    17. T2.MO_SN LIKE 'S46M5%'
    18.  
    19. INNER JOIN
    20. DQC3422 AS T3
    21. ON
    22. T3.STATION='VMI2' AND
    23. T3.LINE_NO=T1.LINE_NO AND
    24. T3.TEST_DTTM=T1.TEST_DTTM AND
    25. T3.DEF_ITEM='OK' AND
    26. T3.MO_SN LIKE 'S46M5%'
    27.  
    28. WHERE
    29. T1.MO_SN LIKE 'S46M%' AND
    30. T1.STATION='RC4' AND
    31. T1.LINE_NO='H5' AND
    32. T1.TEST_DTTM BETWEEN TO_DATE('20180530','yyyymmdd') AND TO_DATE('20180530 235959','yyyymmdd hh24miss')
    33.  
    34. GROUP BY T1.LINE_NO
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    90

    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'
    Code:
    from DQC3412 AS T1

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,093

    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
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    90

    Re: how to Query Data from 2 tables and combine the results in one table

    hi Sir,
    I got it , thanks .

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width