user3684020 user3684020 - 1 year ago 67
SQL Question

In a select add fake rows when a columns have a specifc value

My query is:

SELECT email,price
FROM invoice
WHERE email='123@xxx.com'


I would like to add a fake row every time the field 'price' is equal to '99.00' for every result white the value price '21.00'

Example

Normal result

Email Price
123@xxx.com 84,00
123@xxx.com 99,00
123@xxx.com 29,00
123@xxx.com 99,00


I would like result like this:

Email Price
123@xxx.com 84,00
123@xxx.com 99,00
123@xxx.com 21,00
123@xxx.com 29,00
123@xxx.com 99,00
123@xxx.com 21,00

Answer Source

use the below query to get the expected(modified in order) results If you use this query you won't get the results in the order

Email         Price
123@xxx.com   84,00
123@xxx.com   99,00
123@xxx.com   21,00
123@xxx.com   29,00
123@xxx.com   99,00
123@xxx.com   21,00

However you will get in the below order

Email         Price
123@xxx.com   84,00
123@xxx.com   99,00
123@xxx.com   29,00
123@xxx.com   99,00
123@xxx.com   21,00
123@xxx.com   21,00

The Query to use is

SELECT email,price
FROM invoice
WHERE email='123@xxx.com'
UNION ALL
SELECT email,21,00
FROM invoice
WHERE email='123@xxx.com'
AND Price=99,00