I want to get all fields from row, after give id, but if no rows in table with specified id then I want to return 'not exists' or something. I need this in one query.
Asked
Active
Viewed 186 times
0
Roman Marusyk
- 23,328
- 24
- 73
- 116
mariusz
- 39
- 6
-
Could you, please, make a sample of response you want to get? – Alexey Shein Sep 15 '15 at 09:59
-
if id is in table return *, but if not exists return one 'not exists' or 'not exists' in each column – mariusz Sep 15 '15 at 10:02
-
can u explain what do u mean by : if not exists return one 'not exists' or 'not exists' in each column – Amit.S Sep 15 '15 at 10:08
-
Possible dublicate of [Return a default value if no rows found](http://stackoverflow.com/q/15319264/4275342) – Roman Marusyk Sep 15 '15 at 10:11
-
@mariusz Why do you want to solve this task in SQL? Isn't it easier to substitute default values via programming language that you use? – Alexey Shein Sep 15 '15 at 10:14
-
i need this to xml loop, I don't have permission to engine – mariusz Sep 15 '15 at 10:16
1 Answers
0
I can think only of this solution:
(SELECT * FROM `new_table` WHERE id = 1)
UNION
(SELECT 'not exists!' AS id, 'not exists!' AS name, 'not exists!' AS surname, 'not exists!' AS anotherfield
FROM new_table
WHERE NOT EXISTS (SELECT * FROM `new_table` WHERE id = 1)
);
+----+------+---------+--------------+
| id | name | surname | anotherfield |
+----+------+---------+--------------+
| 1 | aaa | ssss | adad |
+----+------+---------+--------------+
1 row in set (0,00 sec)
(SELECT * FROM `new_table` WHERE id = 0)
UNION
(SELECT 'not exists!' AS id, 'not exists!' AS name, 'not exists!' AS surname, 'not exists!' AS anotherfield
FROM new_table
WHERE NOT EXISTS (SELECT * FROM `new_table` WHERE id = 0)
);
+-------------+-------------+-------------+--------------+
| id | name | surname | anotherfield |
+-------------+-------------+-------------+--------------+
| not exists! | not exists! | not exists! | not exists! |
+-------------+-------------+-------------+--------------+
1 row in set (0,00 sec)
UPDATE: It's quite ugly as you see. Why do you need to solve this task via SQL? Isn't it easier to substitute default values in programming language that you use (I hope)?
Alexey Shein
- 7,342
- 1
- 25
- 38
-
...simpler / less ugly query omitting WHERE NOT EXISTS and uses LIMIT 1 @ http://stackoverflow.com/questions/15319264/return-a-default-value-if-no-rows-found/34758622#34758622 As for why to do this via SQL, for my OOP project use, I am iterating the result object and building table structure from it (column headings and values). By ensuring non-erring queries have 1 row, I don't have to recode the table structure. – mickmackusa Jan 13 '16 at 05:08