Jump to content


Photo

What is wrong with this


  • Please log in to reply
2 replies to this topic

#1 tobeyt23

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

Posted 03 February 2006 - 03:26 PM

Keeps telling me Every derived table must have its own alias for the following:

SELECT zip_code.* FROM zip_code LEFT JOIN zip_tracker ON zip_code.zip_code_id=zip_tracker.zip_code_id,

(SELECT SUM(zc.quantity) AS total FROM zip_code as zc LEFT JOIN zip_tracker zt ON zc.zip_code_id=zt.zip_code_id WHERE zt.order_id='50')

 WHERE zip_tracker.order_id='50'


#2 radox

radox
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts

Posted 03 February 2006 - 05:13 PM

[!--quoteo(post=342403:date=Feb 3 2006, 09:26 AM:name=tobeyt23)--][div class=\'quotetop\']QUOTE(tobeyt23 @ Feb 3 2006, 09:26 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Keeps telling me Every derived table must have its own alias for the following:

SELECT zip_code.* FROM zip_code LEFT JOIN zip_tracker ON zip_code.zip_code_id=zip_tracker.zip_code_id,

(SELECT SUM(zc.quantity) AS total FROM zip_code as zc LEFT JOIN zip_tracker zt ON zc.zip_code_id=zt.zip_code_id WHERE zt.order_id='50')

 WHERE zip_tracker.order_id='50'
[/quote]

should be

SELECT zip_code.* FROM zip_code LEFT JOIN zip_tracker ON zip_code.zip_code_id=zip_tracker.zip_code_id,

(SELECT SUM(zc.quantity) AS total FROM zip_code as zc LEFT JOIN zip_tracker AS zt ON zc.zip_code_id=zt.zip_code_id WHERE zt.order_id='50')

 WHERE zip_tracker.order_id='50'


looks like you just forgot an "AS" for zt

#3 fenway

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

Posted 03 February 2006 - 05:48 PM

No, that's not it -- the AS is optional. However, you _do_ need to give your SUM sub-query a column alias, e.g.:

SELECT zip_code.* FROM zip_code LEFT JOIN zip_tracker ON zip_code.zip_code_id=zip_tracker.zip_code_id,

(SELECT SUM(zc.quantity) AS total FROM zip_code as zc LEFT JOIN zip_tracker zt ON zc.zip_code_id=zt.zip_code_id WHERE zt.order_id='50') AS sumQuantity

WHERE zip_tracker.order_id='50'

Hope that helps.
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