joining the output of 2 select statements

[apefroggie]

Hello All,

If I have two select statements which both outputs a column called name and number of times the name appears and I would like to know what names appear in both results, how do I query for that?

Example - Select 1 output = {paul 3, joe 1, tommy 4}
Select 2 output = {paul 2, jan 2, tommy 1}

Output I want:
prefer: {paul 5, tommy 5}
but it is fine if I just get {paul, tommy}

 

[sleipnir214]

To get the common names, I'd just use something like:

select distinct foo.name
from foo, foo2
where foo.name = foo2.name


Trying to get the aggregate counts from both tables it trickier. I think it can be done, but I'm not sure how.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[swampBoogie]

select name from t1
union
select name from t2

To get the count as well, you need derived tables which is not supported in Mysql. (You can get it by populating a temp orary table with the query

select name from t1
union all
select name from t2

and then do a group by query on the temporary table.)

 

[apefroggie]

Thanks for the response.
After looking at the mysql docs I realized that what I wanted is "intersect" rather than "union", but intersect will only be implemented in future versions. In the meantime, how do I select into a new table or make a temporary table.

 

[sleipnir214]

Selecting into a new table:

http://www.mysql.com/doc/en/INSERT_SELECT.html

Creating a temporary table: Just issue a CREATE TABLE query, but specify a table-type of HEAP. (

http://www.mysql.com/doc/en/HEAP.html)

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[apefroggie]

How do you populate it with something that you selected earlier

 

[sleipnir214]

I'm sorry, but that question does not make any sense to me.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!