help with stored procedure - 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 2005 Forums

SQL Server Administration (2005)

help with stored procedure

Author

Topic

beza
Starting Member

16 Posts

Posted - 2009-02-25 : 16:27:14

Hello

I have a problem that I have no idea how to solve
I have a table with 2 columns
UserID and CarID

I need the ability to provide a CarID and in return select all the CarIDs that the users who bought the carID I provided bought

for example

UserID CarID
1 10
2 10
1 12
2 18

when I provide CarID 10 I want my select to return 10,12,18

is it possible ?? maybe in a stored procedure ??
any help would be greatly appriciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 16:34:21

Yes, it is doable.
The question is how long do you want the "evidence" chain to be?

DECLARE	@Sample TABLE
	(
		UserID INT,
		CarID INT
	)

INSERT	@Sample
SELECT	1, 10 UNION ALL
SELECT	2, 10 UNION ALL
SELECT	1, 12 UNION ALL
SELECT	2, 18

DECLARE	@CarID INT

SET	@CarID = 10

SELECT DISTINCT	s2.CarID
FROM		@Sample AS s1
INNER JOIN	@Sample AS s2 ON s2.UserID = s1.UserID
WHERE		s1.CarID = @CarID

E 12°55'05.63"
N 56°04'39.26"

beza
Starting Member

16 Posts

Posted - 2009-02-25 : 16:48:26

First of all thank you for the quick replay

I didn't understand what did you mean by
The question is how long do you want the "evidence" chain to be?

second ... can you explain what did you do in your stored procedure
I want it to be generic ... I used the numbers only for the example

Thanks a lot!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 17:00:02

[code]CREATE PROCEDURE dbo.spMySearch
(
@CarID INT
)
AS

SET NOCOUNT ON

SELECT DISTINCT s2.CarID
FROM Table1 AS s1
INNER JOIN Table1 AS s2 ON s2.UserID = s1.UserID
WHERE s1.CarID = @CarID[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources