Union/Merge more than one query !

Heya, coool. Welcome to TSDN!

Please use CODE tags when posting source code. See the REPLY GUIDELINES on the right side of the page next time you post.

I think what you are trying to do is join your tables:

Thanks for the nice way you've advised me to use the tags.. :)

do you think the mysql code you've wrote will give the same result of the mysql code I've written above ?

maybe if you explained for me what your code is doing, I can tell if that's really what I'm looking for.. will you do that please ?

thanks in advance,

coool :)

Heya, coool.

I'm not entirely certain what you're trying to accomplish.

The nice thing about using the UNION keyword is that it allows you to return a single result set for multiple queries. The issue with doing it this way is that it is in essence just three separate queries that don't talk to each other.

In my previous post provided an example of a join as one way to approach the situation. It's different than using the UNION keyword because instead of running three separate queries, it runs one query across all three tables looking for similar records.

However, at second glance, you might find it more useful just to use a subquery:

This query runs the three UNION'ed queries and then lumps them together into a single result set. Then in runs through and pulls out only the rows that have a unique value in the `item1` field.

I've got an error : (

here's the exact sql i'm getting: here's the error:
Every derived table must have its own alias

do you have any idea why this is happening ! ..what's the solution ?

Heya, coool.

You're missing a quote mark.

No ! that was just a typo when I've copied and paste to here

ERROR reported:
Every derived table must have its own alias

Heya, coool.

Have a look at this comment.

I've read the comments.. they're very useful

but things didn't work until i added names to my sub-tables

anyway, now i've a new problem with this

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";
[/php]

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 ? : (

Heya, coool.

Sounds like in your main query, you want to use a SUM():

Heya pbmods :)

I've used your structure with some changes - have a look and then read the problem i'm getting !

Result of table1 query is:
item1 = 20
item2 = 5
item3 = 1

Result of table2 query is:
item1 = 40
item2 = 0 - because it's zero it doesn't appear that's okay
item3 = 1

Result of MainTable query is:
item1 = 60
item2 = 5
item3 = 1

here's the problem in item3 :(

I'm getting the sum is 1, where it should be 2

by the way,
table1.item3 is 1234
table2.item3 is 1234-1

so the difference is just (-1)

what do you think of that ?

Heya, coool 8)

In your query, you're only selecting $col1 and $col2, so there shouldn't be an item3 field in any of your result sets.

by the way,
table1.item3 is 1234
table2.item3 is 1234-1

so the difference is just (-1)

what do you think of that ?

I think you're crazy. Heh. Seriously now, I think what you posted is one step behind where you are.

my query is correct

and by item1,2,3 I mean the ouput !!

I just needed to use UNION ALL instead of UNION

and now it's working fine :)

thanks for your help

Heya, coool.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)