Nested Query Doubts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Nested Query Doubts

Author

Topic

nehakapoor2010
Starting Member

7 Posts

Posted - 2010-02-17 : 05:27:15

Hi All,

I am newbie to sql. I have a doubt in Sql.Could some one guide me on this

I am passing an orderID from my application.I need to select fullname,Email and AdminEmail

My table structure is like this

UserDetails
-------------------------------
UserID,FullName,Email,InformationID

Information table
-----------------
InformationID, AdminEmail,UserID

OrderTable
------------

OrderID,InformationID

I have written a query connecting these tables

I will get OrderID from my application, I take ID from Ordertable for that OrderID and place it in a variable.
Then i use a sub query to return fullname, email and Adminemail.I am getting the value corectly. but i need to know my writing of query is correct. Is there any better way to write this query.

CREATE procedure getUserDetails
@orderId int

AS
BEGIN
SET NOCOUNT ON;
Declare @pctId int

if @orderId > 0
Begin

select @Id = InformationID from Orders where Orderid = @orderId
End

if @Id > 0
Begin
select FirstName +' '+ LastName as FullName,Email,Information.AdminEmail from
usersdetails inner join pctInformation
on USers.InformationID=Information.InformationID where UserID in (SELECT UserID
FROM PctInformation
where Information.InformationID=@Id)
End

END

Thanks

Neha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 05:34:58

what you need is this


CREATE procedure getUserDetails 
@orderId int 


AS 
BEGIN 
SET NOCOUNT ON; 
SELECT ud.FullName,ud.Email, i.AdminEmail
FROM UserDetails ud
JOIN Information i
ON i.UserID=ud.UserID
JOIN OrderTable o
ON o.InformationID = i.InformationID 
WHERE o.OrderID=@orderId
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nehakapoor2010
Starting Member

7 Posts

Posted - 2010-02-17 : 05:40:25

I am getting this error when i run the query given

An expression of non-boolean type specified in a context where a condition is expected, near 'JOIN'.

nehakapoor2010
Starting Member

7 Posts

Posted - 2010-02-17 : 05:46:57

That was my mistake. error in column name. Thanks for the query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 05:47:45

quote:
Originally posted by nehakapoor2010

That was my mistake. error in column name. Thanks for the query

nope there was a typo also i fixed it in last post.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources