Using the IN sql Query - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Using the IN sql Query

Author

Topic

shaz123
Starting Member

1 Post

Posted - 2007-03-20 : 07:52:46

Hi
I have the follwing code

Select Dc_Description from tbl_Description where Dc_DescriptionId =(
(Select Tp_DescriptionID from tbl_Type where tp_ID=
(Select Dv_TypeId from tbl_Device where Dv_ID='1')))

To get his to work in vb.net, i need to convert the = sign to IN, and instead of it equaling to one it needs to equal to the result of a text box which will be in characters.

I have tried the follwing but get a syntax error

"Select Dc_Description from tbl_Description where Dc_DescriptionId IN("
Select Tp_DescriptionID from tbl_Type where tp_ID IN
Select Dv_TypeId from tbl_Device where Dv_ID IN " & DeviceID.Text.Trim & ")))

Any help be greatly aprreciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 07:58:13

[code]SELECT DISTINCT Dc_Description
FROM tbl_Description AS d
INNER JOIN tbl_Type AS t ON t.Tp_DescriptionID = d.Tp_DescriptionID
INNER JOIN tbl_Device AS x ON x.Dv_TypeId = t.tp_ID
WHERE x.Dv_ID = '1'[/code]

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-20 : 08:06:20

Also, Make use of Stored procedures for performance improvement and security. By constructing and passing such ad-hoc queries from the front-end, you are increasing the chances of SQL Injection attacks.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-03-20 : 11:37:35

Or Exists Way..


Select Dc_Description from tbl_Description A where
Exists
	(
		Select 1 From Tp_DescriptionID B Where A.	Dc_DescriptionId = B. Tp_DescriptionID
		And  Exists 
			(
				Select 1 from tbl_Device C Where C.Dv_TypeId = b.tp_ID And Dv_ID =  " & DeviceID.Text.Trim & "
			)
		
	)

Chirag

http://chirikworld.blogspot.com/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-20 : 12:27:20

Never concatenate sql statements together like that, always use parameters and/or stored procedures.

see:

http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

not only for security (as mentioned), but because it makes your code cleaner, avoids conversion errors, formatting, delimiting, and so on.

Of course, stored procedures are recommended overall, but if you insist of building SQL strings at your client, you still should *always* use parameters.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources