works in terms of speed.
I assume you are on SQL Server, if not, save this snippet for a rainy day.
I took a stab from your information at how the tables might look. Adjust
accordingly.
CASINO: CASINO_ID, CASINO_NAME, BLACKJACK_TYPE
BLACKJACK: BLACKJACK_TYPE_ID, TYPE_DESCRIPTION
I didn't want to get into using temp tables (# or ##) so I just created a
table to use while getting you recordset for you:
CASINO_BLACKJACK_LINK: CASINO_ID, BLACKJACK_TYPE_ID
Now, create the following stored procedure and pass into it a CASINO_ID and
it will return to you a recordset (BLACKJACK_TYPE_ID,TYPE_DESCRIPTION) for
use in your select. Just think, if you fix the structure, this becomes so
much easier as you know. For now this should get you by. :-)
Mike
---- Procedure below ----
CREATE PROCEDURE SELECT_BLACKJACK_TYPES
@VAR_CASINO_ID int
AS
DECLARE @VAR_SEPARATOR char(1)
SET @VAR_SEPARATOR = ','
DECLARE @VAR_ARRAY varchar(1000)
SET @VAR_ARRAY = (SELECT BLACKJACK_TYPE FROM CASINO WHERE CASINO_ID =
@VAR_CASINO_ID)
SET @VAR_ARRAY = @VAR_ARRAY + @VAR_SEPARATOR
DECLARE @LOCAL_SEPARATOR_POSITION int
DECLARE @LOCAL_ARRAY_VALUE varchar(1000)
DELETE FROM CASINO_BLACKJACK_LINK WHERE CASINO_ID = @VAR_CASINO_ID
SET NOCOUNT ON
WHILE PATINDEX('%' + @VAR_SEPARATOR + '%' , @VAR_ARRAY) <> 0
BEGIN
SELECT @LOCAL_SEPARATOR_POSITION = PATINDEX('%' + @VAR_SEPARATOR +
'%' , @VAR_ARRAY)
SELECT @LOCAL_ARRAY_VALUE = LEFT(@VAR_ARRAY,
@LOCAL_SEPARATOR_POSITION - 1)
INSERT INTO
CASINO_BLACKJACK_LINK
(CASINO_ID,BLACKJACK_TYPE_ID)
VALUES
(@VAR_CASINO_ID,@LOCAL_ARRAY_VALUE)
SELECT @VAR_ARRAY = STUFF(@VAR_ARRAY, 1, @LOCAL_SEPARATOR_POSITION,
'')
END
SET NOCOUNT OFF
SELECT
B.BLACKJACK_TYPE_ID,
B.TYPE_DESCRIPTION
FROM
CASINO_BLACKJACK_LINK L
INNER JOIN BLACKJACK B
ON (L.BLACKJACK_TYPE_ID = B.BLACKJACK_TYPE_ID)
GO
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
