1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Union/Merge more than one query !

Discussion in 'PHP' started by coool, Aug 9, 2007.

  1. coool

    coool New Member

    Hi

    How can I merge the result of few quries that had been unioned ?

    example:
    SELECT item1,item2,item3 FROM table111 WHERE item2='something' GROUP BY item1
    UNION
    SELECT item1,item2,item3 FROM table222 WHERE item2='something' GROUP BY item1
    UNION
    SELECT item1,item2,item3 FROM table333 WHERE item2='something' GROUP BY item1

    NOTE:
    table111, table222, and table333 has the same structure
    always i'm asking for same items to be selected here and for same group by and where statment
    the difference is with the table names
    each table has different records than the other table

    Is there any available code to do this ! any solution !

    do you any idea of how doing this, do you have any simple example ?
     
  2. pradeep

    pradeep Team Leader

    Create a view for the query and then use group by!
     
  3. coool

    coool New Member

    I've got one solution but still have one more problem

    I'm getting the most biggest count from multible tables

    this is what i'm using:

    PHP:
    $fields "Status,Count(names)";
    $table1 "myTable1";
    $table2 "myTable2";
    $groupBy "Status";
     
    $sql "SELECT * FROM((SELECT * FROM (SELECT $fields FROM $table1 GROUP BY $groupBy)AS $table1) UNION
           (SELECT * FROM(SELECT 
    $fields FROM $table2 GROUP BY $groupBy)AS $table2))AS MainTable GROUP BY $groupBy";
    so if my first table gives:
    status1 = 40 items
    status2 = 60 items

    and if my second table gives:
    status1 = 250
    status2 = 20

    the result from the above code is:
    status1 = 250
    status2 = 60

    which is not what i'm looking for (it's just outputing the biggest numbers)

    i want is this answer:
    status1 = 290
    status2 = 80

    what do you think ? : (
     
  4. coool

    coool New Member

    don't worry

    I've figured out the solution

    I just needed to used UNION ALL instead of UNION

    Thanks :)
     

Share This Page