• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 68
  • Last Modified:

if my query returns empty i want to display input as output

Hi Expets,

query:
=======
select ci_id from category where parent_id =236282
UNION
select ci_id from category where parent_id in(select category_id from
category where parent_id=236282)

the above query some times it will returns empty
i want to display 236282 as the output if my query returns empty

can some one help me how to do it.
0
srikotesh
Asked:
srikotesh
1 Solution
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
Try this: (not tested)
IF NOT EXISTS (select ci_id from category where parent_id =236282
UNION
select ci_id from category where parent_id in(select category_id from
category where parent_id=236282)
)
BEGIN 
  SELECT 236282
END
ELSE 
BEGIN
(select ci_id from category where parent_id =236282
UNION
select ci_id from category where parent_id in(select category_id from
category where parent_id=236282)
)
END

Open in new window

0
 
HuaMin ChenSystem AnalystCommented:
Try
select isnull(ltrim(ci_id),'236282') from
(select ci_id from category where parent_id =236282
UNION
select ci_id from category where parent_id in(select category_id from
category where parent_id=236282)) a

Open in new window

0
 
srikoteshAuthor Commented:
hi HuaMinChen,

the above query is not working
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
skullnobrainsCommented:
i assume the category is either itself or it's parent but never both

select
isnull (
  select ci_id from category where parent_id =236282
  ,isnull (
    select ci_id from category where parent_id in(select category_id from
category where parent_id=236282)
    ,236282
  )
)

or maybe

select ci_id from category where parent_id =236282
UNION
select ci_id from category where parent_id in(select category_id from
category where parent_id=236282)
UNION
select 236282 as ci_id
limit 1
0
 
srikoteshAuthor Commented:
I got the solution for this query.

SELECT      * FROM      category WHERE      parent_id = 236282       OR parent_id IN (                   SELECT      category_id                   FROM      category                   WHERE       parent_id = 236282             );
0
 
skullnobrainsCommented:
which lists all children and grandchildren of the given id (same as the query you posted originally)

not sure about this : "i want to display 236282 as the output if my query returns empty" or what you actually wanted in the first place

good for you if you got it working

ps : you can accept your own answer if you don't want to spend your points
0
 
srikoteshAuthor Commented:
Hi skullnobrains,

i am getting the expected result from the second query as u posted

THANKS
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now