Welcome to WebmasterWorld Guest from 54.160.244.240

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:

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!
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members