Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Posts
    21

    Unanswered: Help with query, if value in another table...

    Hello,
    I've been at this for a while so I decided to finally ask. If I have a select statement and I want one of the select columns result to be a BOOL/Int if the value is in another table?

    For Example:

    select cci.hostname, cci.client_group, cci.patch_group, cpsv.patch
    From client_patch_status_view cpsv
    Join ClientCheckIn cci on
    cpsv.client_id = cci.client_id
    AND patch like 'mySimplePatch-1.0'

    Now, I want to add a column to the results called "inPatchGroup" to see if the "patch" is in the patch group patches table "patchgroup_patches".

    How can I do this?

    Thanks,
    tom

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT cci.hostname
         , cci.client_group
         , cci.patch_group
         , cpsv.patch
         , CASE WHEN EXISTS
                     ( SELECT 'booyah'
                         FROM patchgroup_patches
                        WHERE patch = cpsv.patch )
                THEN 1 ELSE 0 
            END AS inPatchGroup
      FROM client_patch_status_view AS cpsv
    INNER
      JOIN ClientCheckIn AS cci 
        ON cci.client_id = cpsv.client_id 
     WHERE cpsv.patch like 'mySimplePatch-1.0'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Posts
    21
    This worked great, thank you very much!

Posting Permissions

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