SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    SELECT 
        ID
      , employerid
      , agencyid 
    FROM [tablename] 
    WHERE 
        (    employerid > 0 
         AND employername LIKE '% %')
    OR
        (    agencyid > 0 
         AND agencyname LIKE '% %')

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Superb thanks Guido... how would I reference another time in the AND EmployerName LIKE part of the syntax?

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    reference another time?

  5. #5
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Not to me
    Maybe you could elaborate a bit more, give some table info and an example maybe?

  7. #7
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    use a UNION query
    Code:
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Brilliant, thanks both of you ... this is working great!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •