SP: Random Records, Fave Record, Plus Known Record, NO repetition.

Question

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!!!

Answer 1

I'm not completely sure I understand you but I believe it goes something like this...

Five random records without #1 One User specific record Record #1 if it's NOT user's specific record.

If so, I believe this does that:

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
UNION ALL
SELECT  TOP 1 *
FROM    (
     SELECT  *
     FROM    TBL_CHARITIES
     WHERE   TBL_CHARITIES.cha_Key NOT IN
             (
             SELECT  members_Favourite
             FROM    TBL_MEMBERS
             WHERE   members_Id = @tmp_ID
             )
     AND (TBL_CHARITIES.cha_Key = '1')
     EXCEPT
     SELECT  *
     FROM    q
     ) tc
END