July 17th, 2000, 05:06 PM
I'm needing to make a list of all customers in my database who have not purchased anything. I can't seem to think of any way but a subquery (not yet implimented) to make this work.
I have the two queries:
1. SELECT DISTINCT custid from customers
2. SELECT DISTINCT custid from orders WHERE subtot > 0 and !voided
I basically need the difference of the two.
Thanks in advance.
July 18th, 2000, 02:34 PM
You'll have to do two queries since mysql does not support sub queries.
If you are using PHP you can do this:
$result=mysql_query("select distinct custid from orders where subtot>0 and !voided");
$result2=mysql_query("select distinct custid from customers where custid !(in('$listed_ids'))");
This will return the array $nosales containing the custids that have no sales.
July 18th, 2000, 02:53 PM
You may be able to get by with a LEFT JOIN, but since I don't know the layout of your tables, this may be a shot in the dark
> select customers.custid
> from customers
> LEFT JOIN orders on customers.custid=orders.custid
> WHERE orders.custid IS NULL;