StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSP: Get 5 random records plus 1
text
Body
copied!<p>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.</p> <p>Here are the two statements i have created so far:</p> <p>Gets favourite charity based on key set in TBL_MEMBERS:</p> <p>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</p> <p>Gets 5 random charities:</p> <p>SELECT TOP 5 * FROM TBL_CHARITIES WHERE cha_Active = 'TRUE' AND cha_Key != '1' ORDER BY NEWID();</p> <p>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?</p> <p>Many Thanks!</p> <hr> <p>Ok! So now ive updated things and got the following:</p> <pre><code>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 </code></pre> <p>Now i need to be able to the record "cha_Key == '1'" but only if its not the users favourite. Is this possible?</p> <p>Thanks for everything so far. ITs truly appreciated.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload