homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Gold Sponsor 2015!
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

MySQL | Complex query

5+ Year Member

Msg#: 4320541 posted 2:27 pm on Jun 1, 2011 (gmt 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..



Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved