Jump to content


Photo

[SOLVED] Subquery


  • Please log in to reply
3 replies to this topic

#1 tobeyt23

tobeyt23
  • Members
  • PipPipPip
  • Advanced Member
  • 259 posts
  • LocationSalisbury

Posted 18 January 2006 - 06:05 PM

When i do the querys together the sum for total returns nothing however when i do them seperately they work fine, what am i doing wrong here:
SELECT zc.* FROM zip_code zc LEFT JOIN zip_holder zh ON zc.zip_code_id=zh.zip_code_id,
(SELECT SUM( zc1.quantity ) FROM zip_code zc1 LEFT JOIN zip_holder zh1 ON zc1.zip_code_id = zh1.zip_code_id WHERE zh1.cart_id='41a0efa279f4a129bb4064835b5a1fb9' GROUP BY zh1.cart_id) AS Total WHERE zh.cart_id='41a0efa279f4a129bb4064835b5a1fb9'


#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 18 January 2006 - 06:19 PM

That is strange; but why don't you have this as a single query? Try the following (UNTESTED):

SELECT zc.*, SUM( zc.quantity ) AS Total FROM zip_code zc LEFT JOIN zip_holder zh ON zc.zip_code_id = zh.zip_code_id WHERE zh.cart_id='41a0efa279f4a129bb4064835b5a1fb9'

Hope that helps.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 tobeyt23

tobeyt23
  • Members
  • PipPipPip
  • Advanced Member
  • 259 posts
  • LocationSalisbury

Posted 18 January 2006 - 06:31 PM

I tried this but when you did it as one you need to GROUP BY which doesn't return what i need

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 18 January 2006 - 08:30 PM

I don't understand why -- you want the SUM() all of the matching rows with the given cartID. The GROUP BY is implicit. Please clarify.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users