I have two table
User
userID username email
1 abc abc@abc.com
2 def def@def.com
3 ghi ghi@ghi.com
Referral
refID userID invEmail
1 1 abc@def.com
2 1 omg@mog.com
3 1 def@def.com
So what i plan is to give a invited user 5 point
meanwhile a inviter 10 point
, so basically userID 1
gained 30 meanwhile userID 2
gained 5. The point i can do in PHP but one part i faced difficulty is when a invEmail to be identified. I don't mind separating into multiple queries if its will work.
How do i show this in sql?
I tried something like
SELECT *, count(r.userID) FROM user u, referral r WHERE u.userID = r.userID OR u.email = r.invEmail GROUP BY r.userID
It returned wrong value.
What i would like it to return, how much count is there inviter and invitee(matched email who has registered based on inviter invitation)
How should i do it?
Thank you.
Edit: i forgot to add something into question, what if i wanted the inviter to receive 10 points only if invitee registered? what i meant is that, only if invEmail exists in u.email then only userID received 10 point. Sorry for my mistake.