php mysql join multiple table

Question

I have two table

User

userID username     email
  1       abc    [email protected]
  2       def    [email protected]
  3       ghi    [email protected]

Referral

 refID userID      invEmail
   1      1      [email protected]
   2      1      [email protected]
   3      1      [email protected]

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.

Can you edit your answer to include A) what it is returning, and B) what you would like it to return? — Aaron
– Aaron, Commented Jan 29, 2012 at 16:41

Brian Hoover · Accepted Answer · 2012-01-29 17:27:41Z

2

You might be able to do something like this:

select points.userId, points.username, points.email, sum(points.points)
FROM
(select u.*, count(*)*10 as points
  from user u
    join referral on u.userID = r.userID
    join user verify_user on r.invEmail = verify_user.email
  group by u.userID, u.username, u.email
UNION
select u.*, count(*)*5 as points
  from user u
    join referral on u.email = r.invEmail and u.userID != r.userID
  group by u.userID, u.username, u.email
) as points
group by points.userId, points.username, points.email

I think you need two separate selects to get the points for each type of registration, combined with a union statement.

edited Jan 29, 2012 at 17:27

answered Jan 29, 2012 at 16:49

Brian Hoover

7,9652 gold badges31 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

cicakman Over a year ago

Wow certainly out of my league, i am trying this solution and will let you know soon. Thanks for helping me out.

cicakman Over a year ago

may i know what this meant? join referral on u.userID = r.refID and u.userID != r.userID as refID is just primary key increasing with auto increment

cicakman Over a year ago

sql FROM is missing from query

Brian Hoover Over a year ago

I need more coffee this morning

cicakman Over a year ago

Hi, 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.

|

Collectives™ on Stack Overflow

php mysql join multiple table

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related