CODEWITHSUNDEEP
mysqlsql
spas2k
spas2k

Reputation: 519

CASE returns multiple rows

I'm trying to determine whether a computer in our organization has certain applications assigned to it. The problem I'm having is that the left join is listing duplicates, listing out every assignment rather than ideally listing the computer name, then 1 if the app(s) is assinged to it or 0 if it doesn't have any of those apps assigned to it. What approach should I take to accomplish this?

The app list can have up to 100+ applications so I need to use an IN. I am unable to modify any of the DB columns.

http://sqlfiddle.com/#!9/a5a387/5

Any help is greatly appreciated!!!

  CREATE TABLE macs
    (`wsid` int, `mach_name` varchar(20))
;
CREATE TABLE assignments
    (`wsid` int, `adp_id` int)
;
INSERT INTO macs
    (`wsid`, `mach_name`)
VALUES
    (1, 'computer1'),
    (2, 'computer2'),
    (3, 'computer3')
;
INSERT INTO assignments
    (`wsid`, `adp_id`)
VALUES
    (1, '100'),
    (1, '101'),
    (1, '103'),
    (2, '100'),
    (2, '101'),
    (3, '100'),
    (3, '101'),
    (3, '103')
     ;

and the query: 

select mach_name,
CASE
   WHEN b.adp_id in(103) THEN 1 ELSE 0 END
from macs a
left join assignments b on a.wsid = b.wsid

Upvotes: 2

Views: 40

Answers (2)

mcnnowak
mcnnowak

Reputation: 295

You could use a correlated sub-query,

SELECT mach_name, COALESCE((SELECT 1 FROM assignments WHERE wsid=m.wsid AND adp_id=103 LIMIT 1), 0) FROM macs m

then COALESCE the potentially NULL value to 0. This is a bit more compact than a CASE statement.

The reason that the OUTER JOIN is returning multiple rows is because rows on the left side of the JOIN can occur multiple times in the right hand side. I.e. there is only one of each machine, but machines can have many assignments. So there are multiple rows that contain a given machine which satisfy your join predicate.

Both the CASE answer and my answer select from the macs table once, and select the result into a column for each machine.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Use exists:

select m.*,
       (case when exists (select 1 from assignments a where a.wsid = w.wsid and a.adp_id in (103)
             then 1 else 0
        end) as flag
from macs m;

Upvotes: 1

Related Questions