Help - Slightly off topic I think

[jemagee]

Ok, so this isn't purely webmastering, but I thought someone here might be able to help

Ok, again I run into a wall with my limited knowledge of SQL so far.

I ran a query on our sales database to find all customers who ordered once in the past year with a given subtotal (10 dollars) using groupby for the ID and the having clause.

701 CustomerIDs were returned. Now I need an easy way to grab the 701 email addresses linked with those customer IDs, I couldn't do a 'join' type query because of the aggrgate function of the first query.

Any Ideas?

[Taltos]

Depends on what SQL you are using. Sounds like you will have to create a temporary table with your SELECT statement. Once you have the temporary table you can do the join you need to do with the temporary table to get the right results.

MS SQL - use: SELECT * INTO tablename FROM ....etc
tablename can be a temporary table that is prefaced with # and you will want to drop after you are finished with it.

MySQL - use: INSERT INTO
Ex:
INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100

Link to manual:
http://dev.mysql.com/doc/refman/5.1/...nto-table.html

If you are using MySQL 5 you can use similar syntax to MS SQL. The is a link to MySQL 5 version of this on the link above.

Let me know if you have more questions.