|
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."
|
|
|
|
|
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.
Do you want to know more?
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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?[^]
Do you want to know more?
|
|
|
|
|
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
|
|
|
|
|
Hello,
I want to call two stored procedures at the same time, so the results are stored in two tables in the same DataSet. Both stored procedures have one parameter with the same name and takes the same value.
I'm using C#. How would I do it?
Thanks,
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Write a SP that calls the other two.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
Hello,
I'm building a stored procedure (for MSDE) in the Access designer. I have a Sales table, and a Packages table (for a car wash). Each sale can have an exterior package and a interior package, each field containing the ID of the record in the Packages table.
I want my stored procedure to return the Name for both the interior and exterior packages. The problem is, if one of the fields has a 0 (no package sold, for example only exterior wash), my stored procedure returns an empty resultset.
I would like it to return that field empty (or NULL) and still return the full row (there's other info on the Sales table like Date and customer ID.)
In case it's important, this is my query (from Access SQL view):
ALTER PROCEDURE dbo.GetSaleInfo
(@ID_sale int)
AS SELECT dbo.Sales.Date, dbo.Sales.Time, dbo.Vehicles.Make + ' ' + dbo.Vehicles.Model AS CarModel, dbo.Packages.Name AS ExteriorPackage,
Packages_1.Name AS InteriorPackage, dbo.Promotions.Name AS Promotion
FROM dbo.Sales INNER JOIN
dbo.Vehicles ON dbo.Sales.ID_vehicle = dbo.Vehicles.ID_vehicle INNER JOIN
dbo.Packages ON dbo.Sales.ExteriorPackage = dbo.Packages.ID_package INNER JOIN
dbo.Promotions ON dbo.Sales.ID_promotion = dbo.Promotions.ID_promotion INNER JOIN
dbo.Packages Packages_1 ON dbo.Sales.InteriorPackage = Packages_1.ID_package
WHERE (dbo.Sales.ID_sale = @ID_sale)Any ideas?
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
I think the simple answer is to use LEFT JOIN instead of INNER JOIN.
Since you want to still return data from dbo.Sales no matter whether you have Exterior package/Interior package or not, you need to specify
Sales LEFT JOIN Packages.
Here's an example of how you should write it:
ALTER PROCEDURE dbo.GetSaleInfo
(@ID_sale int)
AS SELECT dbo.Sales.Date, dbo.Sales.Time, dbo.Vehicles.Make + ' ' + dbo.Vehicles.Model AS CarModel, dbo.Packages.Name AS ExteriorPackage,
Packages_1.Name AS InteriorPackage, dbo.Promotions.Name AS Promotion
FROM dbo.Sales INNER JOIN
dbo.Vehicles ON dbo.Sales.ID_vehicle = dbo.Vehicles.ID_vehicle LEFT JOIN
dbo.Packages ON dbo.Sales.ExteriorPackage = dbo.Packages.ID_package INNER JOIN
dbo.Promotions ON dbo.Sales.ID_promotion = dbo.Promotions.ID_promotion LEFT JOIN
dbo.Packages Packages_1 ON dbo.Sales.InteriorPackage = Packages_1.ID_package
WHERE (dbo.Sales.ID_sale = @ID_sale)
I hope that solves your problem
Edbert P.
Sydney, Australia.
|
|
|
|
|
Edbert P. wrote:
I hope that solves your problem
Yes it did!! Thank you!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Hi all!
Does anyone know if it is possible to find out if a table name already exists using SQL? and if yes what is the code?
Thanks in advance!
vero
|
|
|
|
|
veronique wrote:
Does anyone know if it is possible to find out if a table name already exists using SQL?
Yes.
veronique wrote:
and if yes what is the code?
IF EXISTS(SELECT * FROM sysobjects WHERE xtype='U' AND [name]=@name_of_table)
Do you want to know more?
|
|
|
|
|
thank you Colin!
vero![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
HI,
how could i get all the files in a directory, and list it one per one?
I need to insert the all files included in a directory, i know how to insert in database ( with textcopy ) but i dont know how to have an array with all names of files
thanks
|
|
|
|
|
Hi,
Im new to Stored Procedures and was wondering if there was answer to my question. I have created about 5 stored procedures in SQL Enterprise Manager under "Stored Procedure". One of the parameters is sp_name. Is there a way I can populate a list of the stored procedure names in a table without manually doing so. The idea being is that if the stored_procedures are updated - the table will reflect the changes without having to be manually corrected. Thanking you in advance.
|
|
|
|
|
|
I think it's safer to use the (documented) INFORMATION_SCHEMA views:
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Grimolfr wrote:
I think it's safer to use the (documented) INFORMATION_SCHEMA views
Or the equally documented sysobjects. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-o_4zll.asp[^]
And quite frankly, after using the INFORMATION_SCHEMA views earlier this year... Or should I say attempt to use them. I threw them out and refuse to use them until Microsoft come up with something that is actually useful. Have you actually looked at the source for the views - What a load of complete rubbish!
For example, this is part of the SELECT clause from the ROUTINES view:
SPECIFIC_CATALOG = db_name(),
SPECIFIC_SCHEMA = user_name(o.uid),
SPECIFIC_NAME = o.name,
ROUTINE_CATALOG = db_name(),
ROUTINE_SCHEMA = user_name(o.uid),
ROUTINE_NAME = o.name,
Why have SPECIFIC_CATALOG and ROUTINE_CATALOG? They are the same thing.
Why have SPECIFIC_SCHEMA and ROUTINE_SCHEMA? They are also the same thing.
Why have SPECIFIC_NAME and ROUTINE_NAME? It is just wasting bandwidth and processing time repeating the same data again and again.
Do you want to know more?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
