Jump to content


Photo

Issue with a nested query... solved


  • Please log in to reply
4 replies to this topic

#1 Chips

Chips
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 11 July 2006 - 10:48 AM

select * from Content where section = (SELECT section from Sections where sectionName = 'Welcome')

Now
SELECT section from Sections where sectionName = 'Welcome'
Works fine and returns the value of 1.

This
select * from Content where section = '1'
Works fine and returns the record that I am looking for.

However,
select * from Content where section = (SELECT section from Sections where sectionName = 'Welcome')
Gives an error - in the form of this:

You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT section
FROM Sections
WHERE sectionName =  'Welcome'  )


I have version 4.0.18 installed, using phpmyadmin 2.5.2 (my host controls these obviously) but surely nested queries work fine in these versions? I read up on them as well, but the syntax is exactly (as far as I can see) identical - so I am wondering if anyone can see anything wrong with their fresh eyes.
The two tables are called:
Content
Sections

Sections has section and sectionName as it's fields, section as a tinyint(4), and sectionName as a varchar(50). There is one entry, section = 1, sectionName = Welcome.

Content has a field called section which is also tinyint(4) - the two fields I am matching.
There is one entry (obviously more fields) but the section field is 1... to correspond to the welcome :P

Really struggling to see what maybe the problem, and hoping (as i said) some fresh or more experienced eyes may see a problem. Thanks for your time :)

(Oh yeah, am converting my previously working mssql to mysql queries as I swap databases... hence not that familiar with mysql tbh).

#2 Chips

Chips
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 11 July 2006 - 01:20 PM

Apparently before 4.1 nested queries weren't supported, you had to use joins instead.

Pretty speechless to be honest, would not have ever expected that!

#3 fenway

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

Posted 11 July 2006 - 04:53 PM

JOINs are faster and better anyway, except for the few times when you actually _need_ a sub-query.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 SQL_F1

SQL_F1
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 13 July 2006 - 07:39 AM

Subqueries only had limited support prior to 4.1 (only in INSERT and REPLACE)

You should be able to use a JOIN - careful that with the real data you don't get multiple content rows but limiting with DISTINCT seems appropriate in your scenario

Brian

#5 fenway

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

Posted 13 July 2006 - 10:10 PM

If you want to prevent multiple "content rows", use a GROUP BY clause, not DISTINCT.  DISTINCT means distinct for the entire row, not a particular column, so be careful.
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