First check out this code. I seems like it should work for me, but it doesn't! (surprise!)
Anyway, this is what I tried first:
SELECT
Status as status,
Address as ip,
PCName as pc_name,
(Numbers.Phone = 'CPU/' + PCName) as cpu_contact,
(Numbers.Phone = 'PC/' + PCName) as pc_contact,
(Numbers.Phone = 'LOGIN/' + PCName) as login_contact,
FROM IPAddress
WHERE $where --Generated In code
JOIN Numbers
ON ('CPU/' + PCName = Numbers.Phone)
OR ('PC/' + PCName = Numbers.Phone)
OR ('LOGIN/' + PCName = Numbers.Phone)
So what I want is some boolean calculated fields and to join on similar conditions. I would also like the result to collapse down to single rows. For example, I think the current setup would do something like this:
status ip cpu_contact pc_contact login_contact
-----------------------------------------------
foo bar true false false
foo bar false true false
foo bar false false true
And obviously I'd rather
status ip cpu_contact pc_contact login_contact
-----------------------------------------------
foo bar true true true
Any ideas? Database redesign isn't an option. If it were, I'd do that :-)