TSQL - Using Case Else statements in where clause

Databases
1

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?

2 

SELECT 
    ID
  , employerid
  , agencyid 
FROM [tablename] 
WHERE 
    (    employerid > 0 
     AND employername LIKE '% %')
OR
    (    agencyid > 0 
     AND agencyname LIKE '% %')
3

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

4

reference another time?

5

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?

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

A poor example above…

6

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

7

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.

[TalentTable]
ID, EmployerID, AgencyID

[Employer]
ID, Employername

[Agency]
Id, Agencyname

hope this helps?

8

use a UNION query

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

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

9

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