Select from another statement - 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)

Select from another statement

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2005-07-21 : 14:16:24

Can I do it?

Dim strSQL as string = "select * from Order"
Dim strSQL2 as string = "select orderID from tblShip where orderID in " & strSQL

If not, how to do it?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-07-21 : 14:52:16

1) Avoid Dynamic SQL if you possibly can.

Can you give a real world example of what you are trying to do? My magic

is not working.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2005-07-21 : 14:56:39

I fact, I need sentence like:

select orderID from tblShip where orderID in (select * from Order)

(select * from Order) is stroed in a public variable strSQL

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-07-21 : 14:58:45

An "in" clause must only contain 1 column. The SQL would need to be "Select orderid from tblship where orderid in (select orderid from order)". Either that or you could do an inner join on the tables.

Now for VB or VB.NET there's quite a bit more that you need to do to return data from the database. If you are getting the strSQL back and loading it into a datagrid then you could probably do something in VB with an array or or datatable or something else. I'm not a VB expert and wouldn't know which is better to do.

Daniel
SQL Server DBA
www.dallasteam.com

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-07-21 : 15:23:19

Also, consider joins:

select t.orderID
from tblShip t
inner join
Order o
on t.orderid = o.orderid

...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-07-21 : 22:12:32

you forgot the parentheses and you got the fields mixed up?

quote:
Originally posted by Sun Foster

Can I do it?

Dim strSQL as string = "(select * orderid from Order)"
Dim strSQL2 as string = "select ~~orderID~~ * from tblShip where orderID in " & strSQL

If not, how to do it?

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources