Select 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

Transact-SQL (2000)

Select statement

Author

Topic

GQ
Starting Member

9 Posts

Posted - 2004-02-16 : 17:45:03

Hey all,
I'm back again for another confusing day in T-SQL

I am writing a SP to take information from several tables and insert into one. Here is what I have so far.. I need some help getting the stuff to work correctly any hints would be appreciated.

DELETE from test.dbo.users

INSERT test.dbo.users (userid, phone, queryam, querypm, audioam, audiopm)
SELECT users.userid, users.phone, (SELECT query from stops as queryam where users.stopid1 = stops.stopid),(SELECT query from stops as audiopm where users.stopid2 = stops.stopid),(Select audioid from stops as audioam where users.stopid1 = stopid), (Select audioid from stops as audiopm where users.stopid2 = stopid)
FROM users
left join stops on
users.stopid = stops.stopid

GO

I may be lost in the method which I am trying to use so correct if you can.

Thanks G

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2004-02-16 : 18:09:02

Can you provide more information? like what is it that you are trying to achieve? and what the problem is? any error messages? etc.

--
HTH,
Vyas
http://vyaskn.tripod.com

GQ
Starting Member

9 Posts

Posted - 2004-02-16 : 18:31:22

Well in bases I am creating a table from another server, that will refresh every night.

So I need to get information from three tables
Users
Stops
Audio

and refresh the users table in another database

So I started by deleting everything in the existing table
DELETE from test.dbo.users
Then I thought I would just try an insert statement with multiple selecte statements, but since I never have done this I created this.
INSERT test.dbo.users (userid, phone, queryam, querypm, audioam, audiopm)
SELECT users.userid, users.phone, (SELECT query from stops as queryam where users.stopid1 = stops.stopid),(SELECT query from stops as audiopm where users.stopid2 = stops.stopid),(Select audioid from stops as audioam where users.stopid1 = stopid), (Select audioid from stops as audiopm where users.stopid2 = stopid)
FROM users
left join stops on
users.stopid = stops.stopid

My belief is that I can not do multiple Selects, but I found some code on this site with multiple selects statments in it. When I place this code in the SP box and hit check syntack I get error 207 on stopid. So thats all I know.

Thanks
G

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-02-16 : 23:55:59

You were already half-way there, I can see you have joined the users table to the stops table, but instead of selecting stopid(s) from the joined table, you've created subqueries. Note that you will need to join to the stops table twice, since the users table has two different relations with the stops table. Something like this should work:


INSERT test.dbo.users (userid, phone, queryam, querypm, audioam, audiopm)
 SELECT users.userid, users.phone, stops1.query as queryam, stop2.query as querypm, stops1.audioid     
 as audioam, stops2.audioid as audiopm FROM users
 LEFT JOIN stops AS stops1 ON
 users.stopid1 = stops1.stopid
 LEFT JOIN stops AS stops2 ON
 users.stopid2 = stops2.stopid

As you can see, the Stop table has been joined twice with a different alias to help us identify the correct table.

Also, the basic rule of debugging complex statements is to go step by step. You should separate the SELECT statement first and see if that runs. If that does, then go on to the INSERT statement, at least you know what to expect from the SELECT.

OS

GQ
Starting Member

9 Posts

Posted - 2004-02-17 : 13:11:03

Thanks for the help...

I get it :-)

wow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources