homepage Welcome to WebmasterWorld Guest from 23.20.33.176
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Website
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySQL | Complex query
CodilX




msg:4320543
 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:

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!

 

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.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved