Query problem

[oops4me]

hello,
I have written following query to retrive the values

select a.Comp_name,b.desig_name,c.Ind_name,d.Function_name from Company_Mst a,Designation_Mst b,Industry_Mst c, Function_Mst d where a.Company_id=1 and b.desig_id=778 and Industry_id=147 and Function_id=1;

when i execute this query i am getting following result

Comp_name |Desig_name |Ind_name| Function_name
Microsoft | Manager| IT | Programming

i want to write such query when i give any id value as 0 then it should return me blank record for that perticular id , means as follows

select a.Comp_name,b.desig_name,c.Ind_name,d.Function_name from Company_Mst a,Designation_Mst b,Industry_Mst c, Function_Mst d where a.Company_id=0 and b.desig_id=778 and Industry_id=147 and Function_id=0;

Comp_name Desig_name Ind_name Function_name
|Manager | IT|

(above is Required output)
as i am using and operator the condition is not fulfilling because id 0 is not in table. i ve also used or operator but it is giving me all possible combinations.

so please any body tell me query so that i will get only one record as i required.

Ajay

 

[AtomicWedgie]

maybe with a case?

example:

case function_id
when 0 then ''
else function
end
as function


regards,

Atomci wedgie

 

[oops4me]

hello,
thanks for ur help, but i am not getting ur answer.pls explain more
Ajay

 

[AtomicWedgie]

select case a.Comp_id
when 0 then ''
else comp_name
end as comp_name
, case b.desg_id
when 0 then ''
else b.desig_name
end as desig_name
, case c.industry_id
when 0 then ''
else c.ind_name
end as Ind_name
, case function_id
when 0 then ''
else d.Function_name
end as Function_name
from Company_Mst a,Designation_Mst b,Industry_Mst c, Function_Mst d
where a.Company_id=0 and b.desig_id=778 and Industry_id=147 and Function_id=0;

 

[oops4me]

hello,

you are helping me lot, but the given code is not working. it is giving me output same as i was getting i.e. nothing.

it is showing only column names if i use 0 as id value but not the required result. If i use any other value i.e. other than 0 like 1 then it is showing me proper names of all fields.

please give me the solution

 

[oops4me]

Hello,

i got the solution. i ve added one record to each table where id =0 & its corresponding name=' '. so it is giving me the result as i required. like as follows

comp_id |comp_name
0 |

thanks for ur help

 

[AtomicWedgie]

Oh,

yeah I now see what I did wrong. Didn't understand it properly at first.

Regards,

Atomic Wedgie