[RESOLVED] UNION & GROUP BY (simple)

[david.mitchell]

Hi, i have a problem with using union and group by together.
the following:

SELECT * FROM `table1` UNION SELECT * FROM `table2` WHERE metal = '1' GROUP BY width 

this returns in my dropdown:

2
4
5
6
2
4
5
6

the group by seems to work but only for one table at a time.
i would like it to work with both tables so it should only display 2,4, 5,6.

Any Ideas why its not working correctly?

Regards
David

[xvszero]

Does this work?

SELECT * FROM `table1` WHERE metal = '1' GROUP BY width UNION SELECT * FROM `table2` WHERE metal = '1' GROUP BY width

[david.mitchell]

Sorry, Sadly it still shows duplicate numbers still.

Its like the code before and after UNION are handled like 2 seperate querys.

Im pretty stuck with this.

[xvszero]

I think that's how a UNION works (as far as I know) it just executes multiple statements and then pieces them together. Generally UNION by default is supposed to return distinct results, but maybe try UNION DISTINCT?

[Alan01252]

select * FROM
(
    SELECT * FROM `table1` 
    UNION 
    SELECT * FROM `table2` WHERE metal = '1' 
)
GROUP BY width 

[david.mitchell]

Thanks Alan it had an error as it needs to be defined an alias, but you helped me with the overall design of the statement. Thanks Allot !!

select * FROM
(
    SELECT * FROM `table1` WHERE metal = '1' 
    UNION 
    SELECT * FROM `table2` WHERE metal = '1' 

)AS randomname
GROUP BY width 

[Alan01252]

Whoops, sorry about that! Glad you got it sorted!