Latest Announcemnent
New thread
Status
Not open for further replies.
T

tdsii

Banned
Banned
423
2008
2
0
mysql query help ($5 reward) [SOLVED]

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.wjunction.com/showpost.php?p=605592&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.wjunction.com/showpost.php?p=605634&postcount=12
 
20 comments
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
 
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
 
Why have both ye not included the Date part of the question?

PHP: 
$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)
 
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
 
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
 
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.


PHP: 
$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 >
 
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
 
PHP: 
$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.
 
That won't work either ..

try this:

PHP: 
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 .
 
@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
 
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. :))
 
Lock Down: i guess you understood me.

all queries are wrong. please read again my post to save everyone's time
 
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. :))
 
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.
 
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' )
Click to expand...
// 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
 
Status
Not open for further replies.
Back
Top