Jump to content

Archived

This topic is now archived and is closed to further replies.

Chips

Issue with a nested query... solved

Recommended Posts

[code]select * from Content where section = (SELECT section from Sections where sectionName = 'Welcome')[/code]

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

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

However, [code]
select * from Content where section = (SELECT section from Sections where sectionName = 'Welcome')[/code]
Gives an error - in the form of this:[quote]
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'  )[/quote]

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).

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
JOINs are faster and better anyway, except for the few times when you actually _need_ a sub-query.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.