CODEWITHSUNDEEP
sqlms-accessms-access-2010
sigil
sigil

Reputation: 9546

How to guarantee a row if the query returns no rows?

I have the following union query:

select dataItem,1 as sortOrder from table1
union
select dataItem,2 from table2
order by sortOrder

If the first select query returns one row and the second select query returns no rows, then I get the following result:

dataItem | sortOrder
---------+----------
12345    | 1

I would like to make sure that there is always at least one row, so that if the seconde query returns no rows, we would have:

dataItem | sortOrder
---------+----------
12345    | 1
0        | 2

How can I make this happen?

Upvotes: 1

Views: 125

Answers (2)

kgzdev
kgzdev

Reputation: 2885

You can add one more union and this query checks if there is any row in table2, if no rows then return row with default values

select dataItem,1 as sortOrder from table1
union
select dataItem,2 from table2    
union
select 0,2
where not exists (select 1 from table2)
order by sortOrder

Upvotes: 0

Serg
Serg

Reputation: 22811

The idea is to add union of max(dataItem) or 0 . Not sure on ACCESS syntax

select dataItem,1 as sortOrder from table1
union
select dataItem, 2 from table2
union 
select iif(max(dataItem) is null, 0, max(dataItem)), 2 from table2
order by sortOrder

Upvotes: 2

Related Questions