mysql query help ($5 reward)

[Mr Happy]

$query = $vbulletin->db->query_read(" 
    SELECT p_id
    FROM purchases, client
    WHERE id = cid
        AND id IN 
            (
                SELECT cid 
                FROM purchases 
                WHERE date < 123455667
            )
    "); 

lol just as well you explained it again as I had no clue that that's what you wanted.

The above will give you all the purchases. Just change SELECT p_id to SELECT name or whatever you want to select.

I really hope I understand now what your after and this is what you want as it's getting late here.

[Lock Down]

That won't work either ..

try this:

SELECT p_id
    FROM purchases, client
    WHERE id = cid
        AND id not IN 
            (
                SELECT cid 
                FROM purchases 
                WHERE from_unixtime(date) >  '20100101'
            ) 

The client after purchases should not be needed either. Just purchases not purchases, client .

[Robin H]

@Lockdown, I dont see why Mr Happy is wrong if it's stored as a unix timestamp, there should be no issue with his query. If I'm wrong, I blame the fact that it's over 1AM

[Lock Down]

In his subquery he is checking purchases older than a year and saving clients.

SO he will also get clients that have made purchases less than 1 year. Which is specifically what tdsii was avoiding and couldn't figure out.

In mine I check for purchases made after the year or date and save the clients. Then I check for purchases made by clients who are not in that list. So I avoid agetting the clients who made old prurchases and new purchases.

Hope that wakes you up.

[tdsii]

Lock Down: i guess you understood me.

all queries are wrong. please read again my post to save everyone's time

[Lock Down]

Show me the real names of your tables and the real field names for customer id and purchase date and I can give you a real mysql query.

You can pm me the info if you wish. But based on the garbage names of tables and fields floating around of course it does not work...

The query I gave you is sound but I need real names to give you a real query.

[Robin H]

In his subquery he is checking purchases older than a year and saving clients.
...
Hope that wakes you up.

It did

[tdsii]

we should select the MAX dat for each site and see if its maximum date entry is older than a year from now.
if so we select all its rows. if not, we don't select any of its rows.

[Lock Down]

That is exactly what this query does. I will use the exat names in your first post:
Give me the name of the table and the fields as I asked because that is exactly what this query does.
purchases
---------
p_id
c_id
date

SELECT * FROM purchases WHERE c_id not IN
( SELECT c_id FROM purchases WHERE from_unixtime(date) > '20100101' )

// this part says do not include clients who are in the list below because they made purchases in one year

// this part says select clients who have made purchases less this year

[tdsii]

yeps its my bad when i was changing some attributes.

you got your 5 bucks this one is solved

hurray!