List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:July 26 1999 6:17pm
Subject:Re: create variable problem
View as plain text  
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

Thread
create variable problemRobert Vojta22 Jul
  • Re: create variable problemThimble Smith22 Jul
  • Re: create variable problemChristian Mack26 Jul