Posted by Jeremy on 10/02/21 11:50

I have a situation where i am trying to run two query's and retrieve
one record from each query, then using Union combine them into one
recordset. The First Query is strait forward and should just return
the matching record however the second query needs to find a random
record and return it. The union is causing me some trouble. It seems
that any method i try to find a random record just causes an error.

Here is an example of a query that's causing the problem.
--------------------------------------------------------------------------------------------------------

declare @variable1 smallint
declare @variable2 int

set @variable1 = 10002
set @variable2 = 1001211720

select col1, col2, col3, col4, col5, col6
from table1
where col1 = @variable1 and col2 = @variable2

union

select col1, col2, col3, col4, col5, col6
from table2
where col2 = @variable2
order by newID()

--------------------------------------------------------------------------------------------------------
I have also tried the following which does not provide an error but i
cannot find a way only to return one record from the second query
--------------------------------------------------------------------------------------------------------

declare @variable1 smallint
declare @variable2 int

set @variable1 = 10002
set @variable2 = 1001211720

select newID() as colID, col1, col2, col3, col4, col5, col6
from table1
where col1 = @variable1 and col2 = @variable2

union

select newID() as colID, col1, col2, col3, col4, col5, col6
from table2
where col2 = @variable2
order by colID

If anyone has any ideas, fixes, or thoughts i would appreciate them...
thanks in advance...
FYI i am fairly inexperienced in SQL so please feel free to let me know
if you need more of an explination.

[Back to original message]