TSQL - Using Case Else statements in where clause

I need to check if value in one or two columns is great than 0 and if so filter the search by a where clause.

For example:

SELECT ID,employerid,agencyid from [tablename] WHERE …

case employerid > 0
… WHERE employername LIKE ‘% %’ etc

Case agencyid > 0
… WHERE agencyname LIKE ‘% %’ etc

Any ideas how I can do this?

  , employerid
  , agencyid 
FROM [tablename] 
    (    employerid > 0 
     AND employername LIKE '% %')
    (    agencyid > 0 
     AND agencyname LIKE '% %')

Superb thanks Guido… how would I reference another time in the AND EmployerName LIKE part of the syntax?

reference another time?

Sorry just read my post… what I meant to say is that the EmployerName would be stored in another table and the EmployerId would be the reference for this…

Basically trying to determine what table I should look up, either the employer or agency… does that make sense?

, employerid
, agencyid
FROM [tablename]
( employerid > 0
AND EXISTS IN (SELECT employerid FROM employername WHERE LIKE ‘% %’))
( agencyid > 0
AND agencyname LIKE ‘% %’)

A poor example above…

Not to me :smiley:
Maybe you could elaborate a bit more, give some table info and an example maybe?

LOL yeah sorry…

I have a table with a row that can store Employer Id and Agency Id for storing a list of talent that can be either employer talent or agency talent. I need to be able to display a list of them and be able to search by either employername or agencyname too.

So depending on whether the row is showing a employer id or agency id I need to search the relevant joined table.

ID, EmployerID, AgencyID

ID, Employername

Id, Agencyname

hope this helps?

use a UNION query

SELECT 'employer' AS result_type
     , Employername AS name
  FROM TalentTable
  JOIN Employer
    ON Employer.id = TalentTable.EmployerID
   AND Employer.Employername LIKE '% %'
SELECT 'agency' 
     , Agencyname
  FROM TalentTable
  JOIN Agency
    ON Agency.ID = TalentTable.AgencyID
   AND Agency.Agencyname LIKE '% %'

i still don’t understand why you’re searching for spaces…

Brilliant, thanks both of you … this is working great!