Hi,
I need to get 5 random records from a table plus a further record based on data from the users preferences as stored in a second table.
Here are the two statements i have created so far:
Gets favourite charity based on key set in TBL_MEMBERS:
SELECT DISTINCT TBL_CHARITIES.* FROM TBL_CHARITIES JOIN TBL_MEMBERS ON TBL_CHARITIES.cha_Key = TBL_MEMBERS.members_Favourite WHERE TBL_MEMBERS.members_Id = 16
Gets 5 random charities:
SELECT TOP 5 * FROM TBL_CHARITIES WHERE cha_Active = 'TRUE' AND cha_Key != '1' ORDER BY NEWID();
When used in a stored procedure it only returns the first SELECT statement to my .Net page. How can i combine the two statements whilst ensuring that no results are repeated (Eg the favourite is not present in the 5 random records?
Many Thanks!
Ok! So now ive updated things and got the following:
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
Now i need to be able to the record "cha_Key == '1'" but only if its not the users favourite. Is this possible?
Thanks for everything so far. ITs truly appreciated.