• 0

MYSQL syntax question


Question

if I want to have an embedded select, what is the correct syntax?

I've tried...

SELECT * FROM table1 where ReqID in (select ReqID from table2 where variable = 'some text')

SELECT * FROM table1 where ReqID = (select ReqID from table2 where variable = 'some text')

SELECT * FROM table1 where ReqID in {select ReqID from table2 where variable = 'some text'}

What am I missing... ??

Link to comment
Share on other sites

8 answers to this question

Recommended Posts

  • 0

SELECT * FROM table1 where ReqID = (select ReqID from table2 where variable = 'some text')

This is the correct one the first one, wil probably work, and the third one not at all :cool:

Edit:

In your subquery, you should use:

WHERE variable='some text' LIMIT 1

so only one ReqID is returned and the '=' doesn't fail :woot:

Link to comment
Share on other sites

  • 0

damn, wonder why that isn't working.

If I select the inside SQL

select ReqID from table2 where variable = 'some text'

it runs fine.

If I put one of the values (say 3) I get into the first SQL

SELECT * FROM table1 where ReqID =3

it works fine, but putting that statement together gives me an error

[Main] ERROR 1064: 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 ReqID from table2 where variable= 'some text')

Link to comment
Share on other sites

  • 0

so you are saying...

SELECT * FROM table1 WHERE ReqID IN(SELECT ReqID from table2 WHERE variable= 'some text' LIMIT 1)

Link to comment
Share on other sites

  • 0

what mysql version are you running? Subquerries are suppored since somewhat 4.0.2+ or so IIRC.

if you are on a 3.x.x you are lost I guess.

kindest regards,

Moritz "neofeed" Angermann

Link to comment
Share on other sites

  • 0

When I was reading I found that it might not be supported because of versions differences.

I'm using 4.0.17-nt

I found an alternative way around it, which is more optimized?

select * from table1 where ReqID = (select ReqID from table2 where variable='sometext')
or
select * from table1, table2 where table1.ReqID = table2.ReqID AND table2.variable = 'sometext'

Link to comment
Share on other sites

  • 0

I think it would be more optimized the second one, since subquerys aren't supported, what you can do with the second one is try this:

select table1.field1, table1.field2, table1.field3 from table1, table2 where table1.ReqID = table2.ReqID AND table2.variable = 'sometext'

This way you only return the needed fields and not all of the second table, wich isn't needed.

Link to comment
Share on other sites

  • 0

Gotcha, That sounds like the way I went. I talked to a guy here at work and his impression was that:

the first one is actually two separate statements. the internal select will create a temporary table. this second temporary table will then have to be scanned (since it has no indexes) to find the value that you're looking for.

the second is better cause it'll do everything in one pass with a join. it will also use the indexes already there on the existing tables.

He was referring to MSSQL, not MySQL, so it might be slightly different.

Thanks for the help though guys. I'm going with #2

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.