mysql query help ($5 reward)

[tdsii]

i have the following tables

client
-----
id
name

purchases
---------
p_id
c_id
date
...

i want to select all purchases for all clients where their last purchase was before date X.

For example:
Consider client 1, 2, 3 and 4.
client 1 made 20 purchases before time X and just one recent purchase.
Client 2 made 1000 purchases before time X
Client 3 made only 1 purchase before time X
Client 4 made only 1 purchase recently.

The query should select "all" the purchases for client 2 and 4 only.

first who writes the correct query gets the reward first.

more info: http://www.besthostingforums.com/sho...2&postcount=10

How the query works:
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.

solution: http://www.besthostingforums.com/sho...4&postcount=12

tdsii Reviewed by tdsii on 5th Jan 2011. mysql query help ($5 reward) i have the following tables client ----- id name purchases --------- p_id Rating: 5

[th3fallen]

Edited after realized that c_id was client id lol


$q=("SELECT client.id, client.name, purchases.p_id FROM client INNER JOIN purchases ON client.id=purchases.c_id ORDER BY purchases.date DESC");
while ($r=mysql_fetch_assoc($q)) {
echo $r['p_id'];
}

will loop though and show all pids sorted by day for preticular user

[BlazZ]

I would try a left join. I am not sure if it works because i am a little bit out of practice.

But you could try this:



SELECT p_id FROM purchases, LEFT JOIN client ON (client.id=purchases.c_id) WHERE id = 2 OR id=4;

you should get the id of the purchases. you didn't mention the name of the purchases, you can add it after the SELECT and add a ,

hope i could help

[th3fallen]

oh wow i didnt realize the c_id was client id lmao time to rewrite.

[th3fallen]

corrected mine sorry about that.

[Mr Happy]

Why have both ye not included the Date part of the question?

$query = $vbulletin->db->query_read(" 
    SELECT p_id
    FROM purchases, client
    WHERE id = cid
        AND (cid = 3 OR cid = 4)
        AND date > 123456789
    "); 

Question is confusing. If you don't want the time element leave out AND date > 123456789
If you want it for all clients and not just 3 and 4 then leave out AND (cid = 3 OR cid = 4)

[tdsii]

none are correct, please read again. i mentioned date X (int)

I just want to remove all purchases for outdated clients. Thats all into the story.

OFCOURSE i dont have 4 clients only (i have alot more). so please read the example for illustration

[th3fallen]

i did include the date observe the ORDER BY purchases.date DESC i wasnt sure he if wanted the date sorted or to limit by the date

[Mr Happy]

I don't follow the question still. So each client has a different date?

EDIT oh now I think I follow. the 2 and 4 part are the answer not the question. That's what was confusing me.

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

is the answer so


EDIT 2:
First you say you want to select all purchases before a certain date. Later you say the result should be 2 and 4 but it should really be either 1 and 4 or else 2 and 3.
Depending on what you want you just have to switch the < to >

[tdsii]

ok, its always my mistake in elaborating..

each purchase made have a date. so .. if the last purchase the client made was a year ago. i no longer want ALL his records in the database.

if another client made very old purchase and a very recent purchase as well, i keep the old ones. so its not like remove all before date xxx

i want to remove all purchase records for clients who's purchase is before xxx