Hey :)
I'm having troubles creating a query. I'll try to explain the setup.
This is a project that is based on a virtual currency. The project includes an affiliate system, and the virtual commission for affiliates is a percentage of how much the users spend inside the system. Also, the affiliate system has the option for affiliates, and the parent affiliate receives a small percentage from child affiliate users.
The databases and rows relevant to the query:
users:
id <- user id
affiliate <- affiliate id
etc..
affiliates:
id <- affiliate id
affiliate <- parent affiliate id
etc..
payments:
user_id <- user id who bought something
etc..
My goal is:
Retrieve from the payments table, all payments that include the affiliates users, as well as their affiliate users.
I'm having troubles with joining.. I accomplished one step - I wrote a JOIN query that extracts the users payments that are affiliated to a user. But I can't seem to make a query, where I can also extract all sub-affiliate users.
Here's what I've got:
SELECT DISTINCT
payments.*
FROM
payments INNER JOIN users ON payments.user_id = users.id
WHERE
users.affiliate = '1'
Currently I have a childish PHP query script that builds a query with ANDs and ORs. It has to get all children affiliate users with separate queries and adds each user ID as and AND = 'user_id1', AND = 'user_id2' .. etc , so if an affiliate has 100 sub-affiliates, and each of them have 10 users.. Well you probably understand where I'm going with this :) a very very long query, and also multiple queries that I'd rather do without ..
I hope someone understood my problem and could help me out here.. I have little experience with JOINs, and can't get this darn thing working..
Thanks!
