Please help me finish this query!

[fumeeptc]

I have this query:

SELECT u.username, COUNT(*) AS referrals
FROM users u JOIN
     refferals r
     ON r.uid = u.id JOIN
     users ur
     ON r.reffered = ur.id AND
        ur.joindate >= UNIX_TIMESTAMP( '2017-10-15' ) AND
        ur.joindate < UNIX_TIMESTAMP( '2017-11-16' )
		AND ur.address!=''

GROUP BY u.username
ORDER BY referrals DESC
LIMIT 10

what i need is to sum amount AS points from another table earnings
and make this :

type='+' AND
time >= UNIX_TIMESTAMP( '2017-10-15' ) AND
time < UNIX_TIMESTAMP( '2017-11-16' )
AND points > 200

How can i add this to my query?

[droopsnoot]

You may be better asking in the database section, rather than one specifically aimed at the PHP language.

[fumeeptc]

@droopsnoot thanks changed that

[r937]

SELECT u.username , COUNT(*) AS referrals , ( SELECT SUM(amount) FROM earnings WHERE type = '+' AND time >= UNIX_TIMESTAMP( '2017-10-15' ) AND time < UNIX_TIMESTAMP( '2017-11-16' ) HAVING SUM(amount) > 200 ) AS points FROM users u INNER JOIN refferals r ON r.uid = u.id INNER JOIN users ur ON ur.id = r.reffered AND ur.joindate >= UNIX_TIMESTAMP( '2017-10-15' ) AND ur.joindate < UNIX_TIMESTAMP( '2017-11-16' ) AND ur.address != '' GROUP BY u.username ORDER BY referrals DESC LIMIT 10

[fumeeptc]

@r937

SELECT u.username
, COUNT(*) AS referrals
, ( SELECT SUM(amount)
FROM earnings
WHERE type = ‘+’
AND time >= UNIX_TIMESTAMP( ‘2017-10-15’ )
AND time < UNIX_TIMESTAMP( ‘2017-11-16’ )
HAVING SUM(amount) > 200 ) AS points
FROM users u
INNER
JOIN refferals r
ON r.uid = u.id
INNER
JOIN users ur
ON ur.id = r.reffered
AND ur.joindate >= UNIX_TIMESTAMP( ‘2017-10-15’ )
AND ur.joindate < UNIX_TIMESTAMP( ‘2017-11-16’ )
AND ur.address != ‘’
GROUP
BY u.username
ORDER
BY referrals DESC LIMIT 10

Everything is cool , but i forgot to mention user table that should match r.reffered

[r937]

@fumeeptc

but i forgot to mention user table that should match r.reffered

i figured something was wrong, but i gave you what you asked for

but i think something is still wrong – the user table already links to the refferals table, via r.uid = u.id

maybe you need to add an AND condition to the join