List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:July 22 1999 7:23am
Subject:Re: create variable problem
View as plain text  
Robert,

Maybe you could just create a temporary table, instead of a variable:

CREATE TABLE tmp (first CHAR(200));

INSERT INTO tmp (first) SELECT first FROM table1;
INSERT INTO tmp (first) SELECT first FROM table2;

SELECT first FROM tmp GROUP BY first;

  ** mysql_numrows() holds the answer to your question. **

DROP TABLE tmp;

This is all very hackish because we don't have COUNT(DISTINCT ...)
yet.  If we did, you could do:

CREATE TABLE tmp (count INT);

INSERT INTO tmp (count) SELECT COUNT(DISTINCT first) FROM table1;
INSERT INTO tmp (count) SELECT COUNT(DISTINCT first) FROM table1;

SELECT SUM(count) as the_answer FROM tmp;

DROP TABLE tmp;

With sub-selects, we could do something even nicer.

Tim


At 08:59, 19990722, 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
>  
>--
>
>                    Robert Vojta penguin-at-mendelu-cz
>                  Ustav informatiky - PEF - MZLU v Brne
>                     tel. 05-45132938  ICQ# 27487409
>
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
>posting. To request this thread, e-mail mysql-thread8065@stripped
>
>To unsubscribe, send a message to the address shown in the
>List-Unsubscribe header of this message. If you cannot see it,
>e-mail mysql-unsubscribe@stripped instead.
>
Thread
create variable problemRobert Vojta22 Jul
  • Re: create variable problemThimble Smith22 Jul
  • Re: create variable problemChristian Mack26 Jul