MySQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 
Go Back   Dev Articles Community ForumsDatabasesMySQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old August 29th, 2006, 01:09 AM
gettophil gettophil is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Aug 2006
Posts: 1 gettophil User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 m 42 sec
Reputation Power: 0
MySQL joins, Group id and SUM

ok here is an issue im sure many people have. I have 2 queries which i want to optimise into 1. We have built a sponsorship system where by people can donation money to an individual there by increasing their 'sponsor_total' . If the individual is a member of a team the 'team_total' will also increase. Queries below:

SELECT SUM(p.amount) as sponsor_total
FROM members m
LEFT join donations d on d.member_id = m.id
LEFT join payments p on p.id = d.payment_id
LEFT join teams t on t.id = m.team_id
WHERE m.active = 1 AND m.deleted = 0 AND (d.deleted != 1 or p.amount IS NULL)
group by m.id

SELECT SUM(p.amount) as team_total
from
teams t
LEFT join members m ON m.team_id = t.id
LEFT join donations d on d.member_id = m.id
LEFT join payments p on d.payment_id = p.id
WHERE m.active = 1 AND m.deleted = 0 AND (d.deleted != 1 or p.amount IS NULL)
group by m.team_id

The first gets the sponsors total, the second the team total. We need to do this as part of one query.

Any help is appreciated

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMySQL Development > MySQL joins, Group id and SUM


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway
Stay green...Green IT