complex made simple? - 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)

complex made simple?

Author

Topic

laoh
Starting Member

4 Posts

Posted - 2002-07-18 : 14:17:01

I am building a website on ASP, and I need to return a set of addresses that match a particular profile. Currently I do this in three phases(!);

Phase 1:
I use a simple query to find data
"SELECT address_id FROM users WHERE user_id = '1'"
The data (containing address id numbers like "123;124;125") is returned to String called "SQLTemp"

Phase 2:
I split the String into an array containing the values (in this case) 123, 124 and 125. I then render this array into a String called tempRules like "address_id = 123 OR address_id = 124 OR address_id = 125"

Phase 3:
I use a simple query to find the data
"Select firstname, lastname, homeaddress FROM addresses WHERE" & tempRules

I would appreciate any tips on making this process simpler..

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2002-07-18 : 14:24:56

Could this help?

create procedure get_AddressSet @userid int
as

Select firstname, lastname, homeaddress
from addresses
where address_id in
(
select address_id from users where user_id = @userid
)

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-18 : 14:25:12

How about this:

Select a.firstname, a.lastname, a.homeaddress
FROM addresses a
INNER JOIN users u ON u.address_id = a.address_id
WHERE u.user_id = '1'

Michael

<Yoda>Use the Search page you must. Find the answer you will.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-18 : 14:26:41

Sniped!
I think my solution will run faster though. Try them both out and let us know.

Michael

<Yoda>Use the Search page you must. Find the answer you will.

laoh
Starting Member

4 Posts

Posted - 2002-07-19 : 08:43:40

First of all I would like to thank drymchaser and MichaelP for offering their help, thank you, I really appreciate it.

I now have modified my query to:

Function GetAddresses(ByVal sAddrParentID)
Dim rsAddr
Dim sqlStmt
sqlStmt = FormatOutput( _
"SELECT [a.g_address_id], [a.u_address_name], [a.u_first_name], [a.u_last_name], [a.u_address_line1], [a.u_address_line2], [a.u_city], [a.u_region_name], [a.u_postal_code], [a.u_country_code] FROM Address a INNER JOIN ObjectUsers u ON [a.g_address_id] = [u.u_addresses] WHERE [a.u_address_id]='%1' ORDER BY [a.u_address_name]", _
Array( _
sAddrParentID _
) _
)

Set rsAddr = Server.CreateObject("ADODB.Recordset")
rsAddr.Open sqlStmt, MSCSAdoConnection

Set GetAddresses = rsAddr
End Function

But now I get this error:

Error Type:
Microsoft Commerce OLE DB Provider (0xCA2D8052)
Error in member list parsing, hr: -902987718, url: <Hidden>
/bz/include/addr_lib.asp, line 116

Any help appreciated.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-19 : 10:50:36

Any chance you can post line 116 from your asp code? Or atleast point out what line 116 is.

-----------------------
Take my advice, I dare ya

laoh
Starting Member

4 Posts

Posted - 2002-07-19 : 11:56:34

Line 116 is:

rsAddr.Open sqlStmt, MSCSAdoConnection

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-19 : 12:46:44

What does FormatOutput() do?
I bet that messes it up.

Post the code for that function.

Michael

<Yoda>Use the Search page you must. Find the answer you will.

laoh
Starting Member

4 Posts

Posted - 2002-07-19 : 14:30:55

I removed FormatOutput().. But the same error persists.

SQuirreL
Starting Member

9 Posts

Posted - 2002-07-20 : 12:17:02

laoh,
I noticed a couple of potential problems which you might want to check out. The first involves the placement of the square brackets around the alias.column combinations. The second involves the table chosen for the column in the WHERE clause.

Here is my modification of your code, which I got to work in Query Analyzer (obviously you will want to remove the "DROP TABLE" and "ADD TABLE" statements, or else try it in a database other than one which holds data you want to save!)

I just tried to guess on the table structure based on your previous posts--I may have guessed wrong, which could explain the different results.

It doesn't seem to want to let me post all of this in one reply, so I will reply again with the code....

Paul

SQuirreL
Starting Member

9 Posts

Posted - 2002-07-20 : 12:19:22

quote:

It doesn't seem to want to let me post all of this in one reply, so I will reply again with the code....

-- create tables
CREATE TABLE Address (
g_address_id int NOT NULL,
u_address_name varchar(50) NOT NULL,
u_first_name varchar(50) NOT NULL,
u_last_name varchar(50) NOT NULL,
u_address_line1 varchar(50) NOT NULL,
u_address_line2 varchar(50) NULL,
u_city varchar(50) NOT NULL,
u_region_name varchar(50) NOT NULL,
u_postal_code varchar(50) NOT NULL,
u_country_code varchar(50) NOT NULL
)
GO

CREATE TABLE ObjectUsers (
-- I assume this is some sort of "user id" key
u_address_id int,
-- I assume this references Address.g_address_id
u_addresses int
)
GO

-- populate the tables with some data
INSERT ObjectUsers
VALUES (1, 1)

INSERT Address
VALUES (1, 'home', 'Joe', 'Executive', '3 Sycamore Ln.', Null, 'San Diego', 'CA', '99999', 'US')

INSERT Address
VALUES (1, 'work', 'Joseph', 'Executive', '2000 High Rise', 'Ste. 609', 'San Diego', 'CA', '99999', 'US')

INSERT Address
VALUES (1, 'beach house', 'J.', 'Executive', '45 Some Hidden Place', Null, 'Sea Ranch', 'CA', '91540', 'US')

INSERT Address
VALUES (1, 'condo', 'J.', 'Executive', '3 Some Street', Null, 'San Diego', 'CA', '99999', 'US')

INSERT ObjectUsers
VALUES (2, 7)

INSERT Address
VALUES (7, 'home', 'Sam', 'Programmer', '57 Sycamore Ln.', Null, 'San Diego', 'CA', '99999', 'US')

INSERT Address
VALUES (7, 'work', 'Sam', 'Programmer', '2000 High Rise', 'Ste. 104', 'San Diego', 'CA', '99999', 'US')

-- let's pull out some data!
-- copied and modified from your post
-- for all column references, changed the brackets
-- so they are in this format[alias].[column]
-- Note that since you don't have spaces in the alias
-- or column names, the brackets aren't actually necessary

SELECT [a].[g_address_id],
[a].[u_address_name],
[a].[u_first_name],
[a].[u_last_name],
[a].[u_address_line1],
[a].[u_address_line2],
[a].[u_city],
[a].[u_region_name],
[a].[u_postal_code],
[a].[u_country_code]
FROM Address a INNER JOIN ObjectUsers u
ON [a].[g_address_id] = [u].[u_addresses]
-- In the next line you had the alias "a",
-- but according to the structure described in your original
-- post, I think it should probably be "u" (as I use here)
-- I also didn't know exactly what the "%1" represents in the query
-- (unless that is in fact the user's userid) so I changed it
-- to "1" to match the id's used in my tables.
-- I assume it is somehow related to the function FormatOutput()
WHERE [u].[u_address_id]='1'
ORDER BY [a].[u_address_name]

DROP TABLE Address
GO

DROP TABLE ObjectUsers
GO

Hope this gives you some ideas. If your table structure is different then the one I used, you may want to outline it so that we have a better idea what to go off of....

Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources