SP: Random Records, Fave Record, Plus Known Record, NO repetition.
- by Munklefish
Hi,
Thanks to help from a lot of you guys ive been given the following code which works great. However ive realised ive missed an important bit of info out of the question and so have reposted here (with updated code) to clarify.
The following code gets 5 random records from a table plus a further single record based on the users favourite as identified in a second table:
CREATE PROCEDURE web.getRandomCharities
(
@tmp_ID bigint --members ID
)
AS
BEGIN
WITH q AS
(
SELECT TOP 5 *
FROM TBL_CHARITIES
WHERE cha_Active = 'TRUE'
AND cha_Key != '1'
ORDER BY NEWID()
)
SELECT *
FROM q
UNION ALL
SELECT TOP 1 *
FROM (
SELECT *
FROM TBL_CHARITIES
WHERE TBL_CHARITIES.cha_Key IN
(
SELECT members_Favourite
FROM TBL_MEMBERS
WHERE members_Id = @tmp_ID
)
EXCEPT
SELECT *
FROM q
) tc
END
However, i realised i also need to include the record where "cha_Key == '1'" if it isnt the same as the record returned in the second SELECT statement in the code shown above.
HOpe that makes sense?
THANKS!!!
