Robert Vojta wrote:
>
> Halo,
> got some problem, for instance I've got two tables like that
>
> table 1 - friends
>
> first | last | ....
> -----------------------
> Robert | Vojta
> Zdenek | Pezlar
> Robert | Motycka
>
> table 2 - stupid people
>
> first | last | ....
> -----------------------
> Robert | Uchyl
> Petr | Vojta
> Jan | Sejpka
>
> And now I want know what's the number of unique first names. When I've got
> sybase I made something like that:
>
> create variable num int;
> create variable tmp int;
>
> select count(distinct first) into tmp from table1;
> set num=tmp;
> select count(distinct first) into tmp from table2 where first not in
> (select first from table1)
>
> select num+tmp; // it's what I want
>
> drop variable num;
> drop variable tmp;
>
> How can I make variables in MySQL? It's possible? Or is easiest way how
> to compute distinct first names from 2 tables?
>
> Thanx
> Robert Vojta
Hi Robert
What you search for is called UNION in SQL.
Mysql doesn't provide this yet.
So you either have to make it distinct on the client side, or you have to use a temporary
table like this:
1) CREATE TABLE tmp (first VARCHAR(20) NOT NULL, amount INT NOT NULL);
2) INSERT INTO tmp (first,amount) SELECT first, count(*) FROM friends GROUP BY first;
3) INSERT INTO tmp (first,amount) SELECT first, count(*) FROM stupid_people GROUP BY
first;
4) SELECT first, sum( amount ) FROM tmp GROUP BY first;
Tschau
Christian
