Query problem

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.
AjayI'm not certain if I've understood you correctly but here goes. To
return a row of nulls whenever there is no matching row:
SELECT MAX(A.comp_name), MAX(B.desig_name),
MAX(C.ind_name), MAX(D.function_name)
FROM Company_Mst A, Designation_Mst B, Industry_Mst C, Function_Mst D
WHERE A.company_id = @.company_id
AND B.desig_id = @.desig_id
AND C.industry_id = @.industry_id
AND D.function_id = @.function_id
GROUP BY A.comp_name, B.desig_name, C.ind_name, D.function_name ;
if you only want to return this "blank" row when one of your criteria
is null then add:
HAVING 0 IN (@.company_id, @.desig_id, @.industry_id, @.function_id)
OR A.comp_name IS NOT NULL
(that's assuming comp_name is not nullable - please post DDL and sample
data in future so that we don't have to guess)
If you mean you want a dynamic search condition, see:
http://www.sommarskog.se/dyn-search.html
David Portas
SQL Server MVP
--|||CORRECTION. Assuming only one matching row:
SELECT MAX(A.comp_name), MAX(B.desig_name),
MAX(C.ind_name), MAX(D.function_name)
FROM Company_Mst A, Designation_Mst B, Industry_Mst C, Function_Mst D
WHERE A.company_id = @.company_id
AND B.desig_id = @.desig_id
AND C.industry_id = @.industry_id
AND D.function_id = @.function_id
HAVING 0 IN (@.company_id, @.desig_id, @.industry_id, @.function_id)
OR MAX(A.comp_name) IS NOT NULL ;
David Portas
SQL Server MVP
--
OR MAX(A.comp_name) IS NOT NULL
David Portas
SQL Server MVP
--