Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

MySQL | Complex query

2:27 pm on Jun 1, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:June 10, 2007
posts: 104
votes: 0

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:

id <- user id
affiliate <- affiliate id

id <- affiliate id
affiliate <- parent affiliate id

user_id <- user id who bought something

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:

payments INNER JOIN users ON payments.user_id = users.id
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..