CODEWITHSUNDEEP
mysqlsqlselectjoinin-clause
ak85
ak85

Reputation: 4264

Can't work out why Sub-Query returns more than 1 row

I have the below data which is also in SQL FIDDLE.

    CREATE TABLE completework
    (`id` int, `workerid` int, `checkerid` int);

    CREATE TABLE worker
    (`id` int, `name` varchar(20));

    INSERT INTO completework
        (`id`, `workerid`, `checkerid`)
    VALUES
        (1, 2, 4),
        (2, 3, 5),
        (3, 4, 1),
        (4, 5, 2),
        (5, 1, 3),
        (6, 2, 4),
        (7, 3, 5),
        (8, 4, 1),
        (9, 5, 2),
        (10, 1, 3),
        (11, 2, 4),
        (12, 3, 5),
        (13, 4, 1),
        (14, 5, 2);

    INSERT INTO worker
        (`id`, `name`)
    VALUES
    (1, 'Anthony'),
    (2, 'Bill'),
    (3, 'Claire'),
    (4, 'Daniel'),
    (5, 'Emma');

I want to return results where the worker is either a worker or a checker, so I thought that I would need to union the two columns, but when I try to do this I get the error 

Subquery returns more than 1 row

I have tried the below

    select
    workerid as wid,
    wn.name
    from  completework
    inner join worker wn on completework.workerid=wn.id 
    group by wid

The above just gets the workerid column

    select
    checkerid as cid,
    wn.name
    from  completework
    inner join worker wn on completework.checkerid=wn.id 
    group by cid

The above just gets the checkerid column

    SELECT DISTINCT workedonid FROM (
      SELECT checkerid workedonid FROM completework  
      UNION 
      SELECT workerid workedonid FROM completework
    )c
    ORDER BY workedonid ASC

The above gets an id from either column

    select
        (
            SELECT DISTINCT workedonid FROM (
              SELECT checkerid workedonid FROM completework  
              UNION 
              SELECT workerid workedonid FROM completework
            )c
        ) a,
    wn.name
    from  completework
    inner join worker wn on completework.checkerid=wn.id 
    group by a

In the above I was trying to work the above into a subquery but am getting an error, how can I get around this?

Upvotes: 2

Views: 54

Answers (1)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT DISTINCT w.id AS wid, w.name
FROM worker w
INNER JOIN completework cw ON w.id IN (cw.workerid, cw.checkerid);

Check this SQL FIDDLE DEMO

OUTPUT

| WID |    NAME |
|-----|---------|
|   2 |    Bill |
|   4 |  Daniel |
|   3 |  Claire |
|   5 |    Emma |
|   1 | Anthony |

Upvotes: 2

Related Questions