Query to get row from one table, else random row from another

Question

tblUserProfile - I have a table which holds all the Profile Info (too many fields)

tblMonthlyProfiles - Another table which has just the ProfileID in it (the idea is that this table holds 2 profileids which sometimes become monthly profiles (on selection))

Now when I need to show monthly profiles, I simply do a select from this tblMonthlyProfiles and Join with tblUserProfile to get all valid info.

If there are no rows in tblMonthlyProfile, then monthly profile section is not displayed.

Now the requirement is to ALWAYS show Monthly Profiles. If there are no rows in monthlyProfiles, it should pick up 2 random profiles from tblUserProfile. If there is only one row in monthlyProfiles, it should pick up only one random row from tblUserProfile.

What is the best way to do all this in one single query ?

I thought something like this

select top 2 * from tblUserProfile P LEFT OUTER JOIN tblMonthlyProfiles M on M.profileid = P.profileid ORder by NEWID()

But this always gives me 2 random rows from tblProfile. How can I solve this ?

Answer 1

Try something like this:

SELECT TOP 2 Field1, Field2, Field3, FinalOrder FROM
(
select top 2 Field1, Field2, Field3, FinalOrder, '1' As FinalOrder from tblUserProfile P JOIN tblMonthlyProfiles M on M.profileid = P.profileid
UNION
select top 2 Field1, Field2, Field3, FinalOrder, '2' AS FinalOrder from tblUserProfile P LEFT OUTER JOIN tblMonthlyProfiles M on M.profileid = P.profileid ORDER BY NEWID()
)
ORDER BY FinalOrder

The idea being to pick two monthly profiles (if that many exist) and then 2 random profiles (as you correctly did) and then UNION them. You'll have between 2 and 4 records at that point. Grab the top two. FinalOrder column is an easy way to make sure that you try and get the monthly's first.

If you have control of the table structure, you might save yourself some trouble by simply adding a boolean field IsMonthlyProfile to the UserProfile table. Then it's a single table query, order by IsBoolean, NewID()

Answer 2

In SQL 2000+ compliant syntax you could do something like:

Select ...
From    (
        Select TOP 2 ...
        From tblUserProfile As UP
        Where Not Exists( Select 1 From tblMonthlyProfile As MP1 )
        Order By NewId()
        ) As RandomProfile
Union All
Select MP....
From tblUserProfile As UP
    Join tblMonthlyProfile As MP
        On MP.ProfileId = UP.ProfileId
Where ( Select Count(*) From tblMonthlyProfile As MP1  ) >= 1
Union All
Select ...
From    (
        Select TOP 1 ...
        From tblUserProfile As UP
        Where ( Select Count(*) From tblMonthlyProfile As MP1  ) = 1
        Order By NewId()
        ) As RandomProfile

Using SQL 2005+ CTE you can do:

With 
    TwoRandomProfiles As
    (
    Select TOP 2 ..., ROW_NUMBER() OVER ( ORDER BY UP.ProfileID ) As Num
    From tblUserProfile As UP
    Order By NewId()
    )
Select MP.Col1, ...
From tblUserProfile As UP
    Join tblMonthlyProfile As MP
        On MP.ProfileId = UP.ProfileId
Where ( Select Count(*) From tblMonthlyProfile As MP1  ) >= 1
Union All
Select ...
From TwoRandomProfiles          
Where Not Exists( Select 1 From tblMonthlyProfile As MP1 )
Union All
Select ...
From TwoRandomProfiles
Where ( Select Count(*) From tblMonthlyProfile As MP1  ) = 1
    And Num = 1

The CTE has the advantage of only querying for the random profiles once and the use of the ROW_NUMBER() column.

Obviously, in all the UNION statements the number and type of the columns must match.