Nsted SELECT statements
-
Hello is it possible in SQL (and specially SQL Server 2000) to perform a SELECT on result of another SELECT statement? something like this just as an example of what I am talking about: SELECT * FROM (SELECT a.Name, a.EMail FROM Users a WHERE a.Gendre = 0) WHERE Name = 'Danielle' Thanks for any note, -Den --- "Art happens when you least expect it."
As Christian said, why do you want to do this? Surely it would be easier to write
SELECT Name, Email FROM Users WHERE Gendre = 0 AND Name='Danielle'Of course it is a very useful thing if you want to perform inner joins on subqueries, but I found recently that the query optimiser can sometimes get itself in a bit of a fankle over that if the subquery operates on too much data: The Stored Procedure runs how fast?[^]
-
SELECT myTable.* FROM (SELECT a.Name, a.EMail FROM Users a WHERE a.Gendre = 0) myTable WHERE Name = 'Danielle' You need to provide an alias. Why do you want to do this ? It means the data needs to be processed twice. Christian I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
HEllo Christian, Thank you so much for your reply The example I used was not my real requirement and as Colin said it could be easily relpaiced by an "AND". I have a rather complicated query in which I really need such an approach Thank you again --- "Art happens when you least expect it."
-
HEllo Christian, Thank you so much for your reply The example I used was not my real requirement and as Colin said it could be easily relpaiced by an "AND". I have a rather complicated query in which I really need such an approach Thank you again --- "Art happens when you least expect it."
Yeah, I guessed it was an example :-) I've sometimes felt I needed to do something similar, but I've always found a better performance alternative. In fact, we're ot allowed to write queries like this at work, and we write some complex queries Perhaps you could provide an example closer to what you're doing ? Christian I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
-
As Christian said, why do you want to do this? Surely it would be easier to write
SELECT Name, Email FROM Users WHERE Gendre = 0 AND Name='Danielle'Of course it is a very useful thing if you want to perform inner joins on subqueries, but I found recently that the query optimiser can sometimes get itself in a bit of a fankle over that if the subquery operates on too much data: The Stored Procedure runs how fast?[^]
Hello Colin Thanks for your note, you are right about that query but that was just an example. ( not a good one I think :-) ) and again you are right my real project is some INNER JOINs that I have found this a good way to achive the result --- "Art happens when you least expect it."
-
Yeah, I guessed it was an example :-) I've sometimes felt I needed to do something similar, but I've always found a better performance alternative. In fact, we're ot allowed to write queries like this at work, and we write some complex queries Perhaps you could provide an example closer to what you're doing ? Christian I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
Christian Graus wrote: In fact, we're ot allowed to write queries like this at work Do you mean as per the example? Or using a subquery in an inner join? Either way, isn't it a bit restrictive. Surely a better approach would be to write the query which ever way makes most sense and then if it is too slow profile it to see where the bottle necks are and rewrite those sections.
-
Christian Graus wrote: In fact, we're ot allowed to write queries like this at work Do you mean as per the example? Or using a subquery in an inner join? Either way, isn't it a bit restrictive. Surely a better approach would be to write the query which ever way makes most sense and then if it is too slow profile it to see where the bottle necks are and rewrite those sections.
Colin Angus Mackay wrote: using a subquery in an inner join? Bingo Colin Angus Mackay wrote: Either way, isn't it a bit restrictive. The actual rule is, if you think you need to do it, ask and they'll show you why you don't :-) It works, I always find for myself why I don't. Christian I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
-
Hello Colin Thanks for your note, you are right about that query but that was just an example. ( not a good one I think :-) ) and again you are right my real project is some INNER JOINs that I have found this a good way to achive the result --- "Art happens when you least expect it."
Yeah, joins are generally the answer rather than what you were doing before :-) Christian I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
-
Yeah, I guessed it was an example :-) I've sometimes felt I needed to do something similar, but I've always found a better performance alternative. In fact, we're ot allowed to write queries like this at work, and we write some complex queries Perhaps you could provide an example closer to what you're doing ? Christian I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
hi nice to see ur provided info anbout Select Subquries... but u alos mentioned that u are not allowed to use such queries at work..then what u do ...where subquries are needed...and u don't have an alternate???? is there any more accurae and efficient way as an alternate to these sub-quries. ................................ munawar
-
hi nice to see ur provided info anbout Select Subquries... but u alos mentioned that u are not allowed to use such queries at work..then what u do ...where subquries are needed...and u don't have an alternate???? is there any more accurae and efficient way as an alternate to these sub-quries. ................................ munawar
munawarhussain wrote: then what u do ...where subquries are needed They generally are not needed, that's the point. munawarhussain wrote: is there any more accurae and efficient way as an alternate to these sub-quries. I usually find a way to use joins rather than subqueries. Obviously, if there physically is no other way, we'd use a subquery, but as a rule we're able to eliminate them. Christian I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
-
munawarhussain wrote: then what u do ...where subquries are needed They generally are not needed, that's the point. munawarhussain wrote: is there any more accurae and efficient way as an alternate to these sub-quries. I usually find a way to use joins rather than subqueries. Obviously, if there physically is no other way, we'd use a subquery, but as a rule we're able to eliminate them. Christian I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
Christian..thanks for reply.. may i know, in case, when u want to delete records is there any other way to delete data from more than one table with out using subquries?? in selecting data from multiple tables its possible to use joins.. okkkk
-
Christian..thanks for reply.. may i know, in case, when u want to delete records is there any other way to delete data from more than one table with out using subquries?? in selecting data from multiple tables its possible to use joins.. okkkk
You can use joins to delete as well. You can also use them to update. Christian I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer