joins of two tables +search

regin

hi... plz somone help me write these queries.. i want to search the data's from employee select the employee name,designation_name where 1)employee code that i can pass or else designation name i can pass 2)only one i can pass from theses 2 3)the employee can contain null value for desination name 4)constraint is :employee.designation_id=desig.designation_id plz somone help me out to solve this pblm

pmarfleet

What part of your code are you specifically having difficulties with? At the moment, it sounds like you want someone to do your work for you. You haven't stated what database you are you using. However if it is SQL Server, I suggest you do some research on writing stored procedures to encapsulate the logic for your search.

Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush

andyharman

For SQL-Server:

select employee.name, designation.name
from employee
left outer join designation
  on designation.designation_id = employee.designation_id
where employee.code = IsNull(@EmployeeCode, employee.code)
and IsNull(designation.name, '') = IsNull(@DesignationName, designation.name)
order by employee.name

The outer join links the two tables together - even if there is no designation. The IsNull bit allows you to optionally pass in a NULL or a proper value for the employee code and the designation. Hope that helps. Andy