+chorpeac MVC Posted July 16, 2004 MVC Share Posted July 16, 2004 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 More sharing options...
0 GatorV Posted July 16, 2004 Share Posted July 16, 2004 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 More sharing options...
0 +chorpeac MVC Posted July 16, 2004 Author MVC Share Posted July 16, 2004 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 More sharing options...
0 +chorpeac MVC Posted July 16, 2004 Author MVC Share Posted July 16, 2004 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 More sharing options...
0 +chorpeac MVC Posted July 16, 2004 Author MVC Share Posted July 16, 2004 That code isn't working either...I'm missing something... Link to comment Share on other sites More sharing options...
0 neofeed Posted July 16, 2004 Share Posted July 16, 2004 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 More sharing options...
0 +chorpeac MVC Posted July 16, 2004 Author MVC Share Posted July 16, 2004 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 More sharing options...
0 GatorV Posted July 16, 2004 Share Posted July 16, 2004 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 More sharing options...
0 +chorpeac MVC Posted July 16, 2004 Author MVC Share Posted July 16, 2004 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 More sharing options...
Question
+chorpeac MVC
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