#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    San Angelo, TX, US
    Posts
    3
    Rep Power
    0
    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.
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    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");
    while (list($data[])=mysql_fetch_row($result)){}
    $listed_ids=implode(',',$data);

    $result2=mysql_query("select distinct custid from customers where custid !(in('$listed_ids'))");
    while (list($nosales[])=mysql_fetch_row($result2){}

    This will return the array $nosales containing the custids that have no sales.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 1999
    Location
    Annapolis, Maryland US
    Posts
    113
    Rep Power
    16
    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;

Similar Threads

  1. can't find error in subquery...
    By iso in forum MySQL Help
    Replies: 12
    Last Post: December 29th, 2003, 03:18 PM
  2. Re-using a MySQL connection for subquery
    By Casper in forum PHP Development
    Replies: 4
    Last Post: December 5th, 2003, 04:40 PM
  3. Simulate subquery
    By dojjan77 in forum MySQL Help
    Replies: 0
    Last Post: June 11th, 2003, 06:21 AM
  4. Help converting subquery
    By bzzz in forum MySQL Help
    Replies: 4
    Last Post: April 7th, 2003, 04:54 PM
  5. Replies: 3
    Last Post: March 17th, 2003, 02:02 PM

IMN logo majestic logo threadwatch logo seochat tools logo