SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Query Union

  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Query Union

    Hello everyone, I need your help.

    I have five tables in my db mysql: tbl_A, tbl_b, tbl_C, tbl_D and tbl_E and I execute this four query:

    Code:
    SELECT COUNT(idDTES) AS stridDTES 
          , SUM(CL_BT_DIS) AS strCL_BT_DIS 
          , SUM(AV) AS strAV
          , COUNT(SC) AS strL
          , COUNT(idDTES) - COUNT(SC) AS strB 
       FROM tbl_A 
    GROUP BY DATE_E
    
    ***************
    
    SELECT SUM(Rif_AV) AS strRif_AV
         , SUM(Rif_n_CL) AS strRif_n_CL 
       FROM tbl_B
    
    ***************
    
    SELECT SUM(tbl_C.cl_BT) AS strCl_BT
         , COUNT(tbl_D.EVENT) AS strEVENT 
       FROM tbl_C 
          INNER JOIN tbl_D ON tbl_D.LINE = tbl_C.COD_LINE 
    GROUP BY DATE_E 
    
    ***************
    
    SELECT SUM(N_S_TR) AS strNSTR FROM tbl_E GROUP BY DATE_E
    There is possible to have same output with only query combining the four queries?

    Any help would be very much appreciated and many thanks to any who can help me.

    Thanks in advance.
    Chevy

  2. #2
    SitePoint Enthusiast golotyuk's Avatar
    Join Date
    Aug 2007
    Location
    Kiev, Ukraine
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want to union theese results, you should change your selected columns for all queries so their count will be the same (if you select 3 columns from first table, you should select 3 from second and third as well)

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Many thanks x your help.

    I have JOIN queries 1 and 2 and new query working, but I don't see for queries 3 and 4...:

    Code:
    SELECT 
      COUNT(A.idDTES) AS stridDTES 
    , SUM(A.CL_BT_DIS) AS strCL_BT_DIS 
    , SUM(A.AV) AS strAV 
    , COUNT(A.SC) AS strL 
    , COUNT(A.idDTES) - COUNT(A.SC) AS strB 
    , SUM(CA.Rif_AV) AS strRif_AV
    , SUM(CA.Rif_n_CL) AS strRif_n_CL 
    
      FROM 
         tbl_A A INNER JOIN tbl_B CA ON A.idDTES = CA.DM 
    GROUP BY DATA_E

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    That's not a UNION, that's a JOIN

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    that's not a union, that's a join
    ok

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by cms9651 View Post
    Many thanks x your help.

    I have JOIN queries 1 and 2 and new query working, but I don't see for queries 3 and 4...:

    Code:
    SELECT 
      COUNT(A.idDTES) AS stridDTES 
    , SUM(A.CL_BT_DIS) AS strCL_BT_DIS 
    , SUM(A.AV) AS strAV 
    , COUNT(A.SC) AS strL 
    , COUNT(A.idDTES) - COUNT(A.SC) AS strB 
    , SUM(CA.Rif_AV) AS strRif_AV
    , SUM(CA.Rif_n_CL) AS strRif_n_CL 
    
      FROM 
         tbl_A A INNER JOIN tbl_B CA ON A.idDTES = CA.DM 
    GROUP BY DATA_E
    You're grouping by DATA_E, but that field isn't in the select. There isn't any field in the select, just sums and counts. So how many rows does this query return?
    And how do you know what each row contains if you don't select some identifying field as well (for example DATA_E)?

  7. #7
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Many thanks for your help, I have change method.


Bookmarks

Posting Permissions

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