Jump to content


Photo

sql worked with some version and not with other


  • Please log in to reply
3 replies to this topic

#1 hvle

hvle
  • Members
  • PipPipPip
  • Advanced Member
  • 667 posts
  • Locationmelbourne, Australia

Posted 02 August 2006 - 04:28 PM

hi guys,

i have a table classified_city which contains
idn: interger
name: varchar
subcity_idn: integer

I have this query:
"select name from classified_city where idn=(select subcity_idn from classified_city where idn='2')"

This query does work on my local server, which run mysql 4.1.12 but does not work on remote server which I presume running a older version.  (i do not know which version they're running).

Can anybody correct the syntax of that query so it would work with older version?


Thanks



Life's too short for arguing.

#2 fenway

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

Posted 02 August 2006 - 04:56 PM

You might as well use a self-join (UNTESTED):

select c1.name from classified_city AS c1 
join classified_city AS c2 on ( c1.idn = c2.subcity_idn )
where c2.idn='2'

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

#3 hvle

hvle
  • Members
  • PipPipPip
  • Advanced Member
  • 667 posts
  • Locationmelbourne, Australia

Posted 03 August 2006 - 04:32 AM

Thank you so much,
It worked on both server, and faster too.


Life's too short for arguing.

#4 fenway

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

Posted 03 August 2006 - 02:14 PM

No problem.. JOINs are almost always faster than sub-queries, more portable, and IMHO, easier to understand and maintain.
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