workaround for Union in mySQL

jalperin

I'm trying to Count fields in two different tables, but want the results to display in a single query. I'm using mySQL which doesn't yet support Union. Could someone outline a general procedure for working around this, perhaps using a temporary table? (I'm pretty new to SQL). thanks! --Jeff

sarahk

getting your results and displaying them are 2 different actions. What would be the impact of running 2 queries and adding the result together and then displaying.

The query would be the same effectively, the performance impact would come from having to send 2 requests not 1. I would assume that that would be ok and test to see if it's not.

jalperin

Let me be more specific about what I'm trying to do. I want to create a query that would generate output like this:

Client Total # Clicks Total # Opt-ins

==== ========== ==========

Bob 5 10

Fred 7 12

This would be easy, except I have two “transaction” – type tables:

Table Clicks keeps track of one type of transaction (in this case, clicking on a link embedded in an email), and Table Opt-Ins keeps track of another type of transaction (in this case, agreeing to become part of an email list). Each of the transaction tables contains a field for Client (actually a pointer to a Client Table).

I can easily display what I want in two separate queries:

#1: SELECT client.name, count(clicks.id) FROM clicks, client WHERE clicks.client=client.id GROUP BY clicks.client

#2: SELECT client.name, count(optins.id) FROM optins, client WHERE optins.client=client.id GROUP BY optins.client

My question: is there a technique to be able to combine the results in a single query so I can display them in a single output table?

In reading some other posts, it seems that a Union of the two queries would give me what I need, but unfortunately, my version of mySQL doesn't support that.

sarahk

It's still not too bad. Run each query then construct an array with 3 columns - name, total1, total2

run through the first query results and add them to the array with no checking.

run through the second array and update total2 if the user is already in there, create a new row where the user is new

run through the array and output to html.

jalperin

got it; that makes sense. I read some comments about using temporary tables, but what you're describing is conceptually simpler (at least for me). thanks.