Union/Merge more than one query !

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
coool
Forum Commoner
Posts: 45
Joined: Wed Jul 11, 2007 5:51 pm

Union/Merge more than one query !

Post by coool »

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 ?
coool
Forum Commoner
Posts: 45
Joined: Wed Jul 11, 2007 5:51 pm

Post by coool »

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:

Code: Select all

$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 ? : (
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

table111, table222, and table333 has the same structure
Why isn't the data all in one table then?
coool
Forum Commoner
Posts: 45
Joined: Wed Jul 11, 2007 5:51 pm

Post by coool »

don't worry

my query now is working fine

I just used UNION ALL instead of UNION

thanks :)
Post Reply