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
    69

    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: 125
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
    1,466

    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
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    69

    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
    1,466

    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
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    69

    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