Union/Merge more than one query !

[coool]

Hi

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

example:[code=mysql]
SELECT item1,item2,ite m3 FROM table111 WHERE item2='somethin g' GROUP BY item1
UNION
SELECT item1,item2,ite m3 FROM table222 WHERE item2='somethin g' GROUP BY item1
UNION
SELECT item1,item2,ite m3 FROM table333 WHERE item2='somethin g' GROUP BY item1[/code]

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 ?

[pbmods]

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:
[code=mysql]
SELECT
*
FROM
(
`table111`
LEFT JOIN
`table222`
AS `t2`
USING(`item1`)
LEFT JOIN
`table333`
USING(`item1`)
)
WHERE
`t2`.`item2` = 'something'
GROUP BY
`t2`.`item1`;
[/code]

[coool]

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 :)

[pbmods]

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:

[code=mysql]
SELECT
DISTINCT
*
FROM
(
(
SELECT
`item1`,
`item2`,
`item3`
FROM
`table111`
WHERE
`item2` = 'something'
)
UNION
(
SELECT
`item1`,
`item2`,
`item3`
FROM
`table222`
WHERE
`item2` = 'something'
)
UNION
(
SELECT
`item1`,
`item2`,
`item3`
FROM
`table333`
WHERE
`item2` = 'something'
)
)
GROUP BY
`item1`
[/code]

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.

[coool]

I've got an error : (

here's the exact sql i'm getting:[code=mysql]
SELECT DISTINCT * FROM ((SELECT item1,item2 FROM table1 WHERE item3='4' ) UNION (SELECT item1,item2 FROM table2 WHERE item3='4 )) GROUP BY item2[/code]

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 ?

[pbmods]

Heya, coool.

You're missing a quote mark.

[coool]

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

[code=mysql]
SELECT DISTINCT * FROM ((SELECT item1,item2 FROM table1 WHERE item3='4' ) UNION (SELECT item1,item2 FROM table2 WHERE item3='4' )) GROUP BY item2
[/code]

ERROR reported:
Every derived table must have its own alias

[pbmods]

Heya, coool.

Have a look at this comment.

[coool]

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(n ames)";
$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 ? : (

[pbmods]

Heya, coool.

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

[code=mysql]
SELECT
SUM(`count`)
FROM
(
(
SELECT
*
FROM
(
SELECT
COUNT(`fieldnam e`)
AS `count`
FROM
$table1
GROUP BY
$groupBy
)
AS $table1
)
UNION
(
SELECT
*
FROM
(
SELECT
COUNT(`fieldnam e`)
AS `count`
FROM
$table2
GROUP BY
$groupBy
)
AS $table2
)
)
AS
MainTable
GROUP BY
$groupBy
[/code]

[coool]

Heya pbmods :)

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

[code=php]
$sql = "
SELECT $col1,SUM($col2 ) FROM
(
(SELECT * FROM
(SELECT $col1, COUNT($col2) AS $col2 FROM $table1 GROUP BY $col1)
AS $table1)

UNION

(SELECT * FROM
(SELECT $col1, COUNT($col2) AS $col2 FROM $table2 GROUP BY $col1)
AS $table1)
)
AS MainTable GROUP BY $col1";
[/code]

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 ?

[pbmods]

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.

[coool]

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

[pbmods]

Heya, coool.

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