user3684020 user3684020 - 7 months ago 17
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

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
Comments