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

tjones1105 · #1 (**permalink**) 02-22-11, 18:43

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

r937 · #2 (**permalink**) 02-22-11, 23:13

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'

tjones1105 · #3 (**permalink**) 02-24-11, 10:19

This worked great, thank you very much!

Internet Services

Web Development

Digital Marketing

Consumer Tech