SQL Consolidating Multiple rows

Question

I'm simplifying the problem, but the concept here should be the same...

Let's say we have a table FOO with fields ID and ATTRIB, and a table BAR with fields ID, A_MATCHING_ID, STATUS. These tables look like this:

Table FOO:

ID    -     ATTRIB
=====
1001  -     ATR_A
1002  -     ATR_B
1003  -     ATR_A
1004  -     ATR_B
1005  -     ATR_B

etc.

Table BAR:

ID    -     FOO_MATCHING_ID   -     STATUS
=====
9901  -     1001              -     STAT_A
9902  -     1001              -     STAT_A
9903  -     1001              -     STAT_B
9904  -     1002              -     STAT_C
9905  -     1002              -     STAT_B
9906  -     1002              -     STAT_B
9907  -     1003              -     STAT_A

etc.

There is a one-to-many relationship of FOO to BAR.

I want to run a query on these two tables with a calculated/dependent field [HAS_STAT_A]:

Expected Query Result:

FOO_ID      -     HAS_STAT_A
=====
1001        -     TRUE
1002        -     FALSE
1003        -     TRUE

How can I accomplish this?

SELECT FOO.ID AS FOO_ID, [FILL THIS IN] AS HAS_STAT_A 
FROM FOO 
JOIN BAR ON FOO.ID = BAR.FOO_MATCHING_ID;

I thought of using CASE, but don't know how to use that across multiple records. I also thought of adding in some extra sub-queries, but wasn't sure how to arrange those, either.

This is similar, but not quite what I want to achieve. It led to me to something like the below, but I get GROUP BY errors. When I add GROUP BY, I get MISSING KEYWORD errors.

SELECT FOO.ID AS FOO_ID, 
    INSTR (LISTAGG (BAR.STATUS, ',') WITHIN GROUP (ORDER BY BAR.STATUS), 'STAT_A') AS HAS_STAT_A 
FROM FOO 
JOIN BAR ON FOO.ID = BAR.FOO_MATCHING_ID;

score 4 · Accepted Answer · edited Dec 19 '12 at 15:26

4

select  foo.id as foo_id
,       case 
        when max(case when lower(trim(bar.status)) = 'stat_a' then 1 end) = 1 then 'true' 
        else 'false' 
        end as has_stat_a 
from    foo 
join    bar
on      foo.id = bar.foo_matching_id
group by
        foo.id

edited Dec 19 '12 at 15:26

Mariappan Subramanian

9,527
8
32
33

answered Dec 19 '12 at 15:21

Andomar

232,371
49
380
404

This appears to make sense, so thank you. However, I am getting an error: `ORA-00979: not a GROUP BY expression`, though I am using a valid field to `GROUP BY`. – Gaffi Dec 19 '12 at 15:57
If there are rows in `foo` that have no matching rows in `bar`, these rows won't show up in the query result (since you're using an inner join), which may not be what you want. – Brian Camire Dec 20 '12 at 02:02
@BrianCamire Thanks for that extra advice. Luckily, I know (*or at least I think I do; it's not my DB, I'm just grabbing data*) that there will always be a match. – Gaffi Jan 02 '13 at 13:39

score 0 · Answer 2 · answered Dec 19 '12 at 15:32

Here's one way to do it

SELECT 
    FOO.ID AS FOO_ID, 
    SUM(CASE WHEN BAR.[STATUS]='STAT_A' THEN 1 ELSE 0 END) AS HAS_STAT_A
FROM FOO 
    INNER JOIN BAR ON FOO.ID = BAR.FOO_MATCHING_ID
GROUP BY
    FOO.ID

You can also take this and filter out the rows where has_stat_a is 0 by using the "having" keyword

SELECT 
    FOO.ID AS FOO_ID, 
    SUM(CASE WHEN BAR.[STATUS]='STAT_A' THEN 1 ELSE 0 END) AS HAS_STAT_A
FROM FOO 
    INNER JOIN BAR ON FOO.ID = BAR.FOO_MATCHING_ID
GROUP BY 
    FOO.ID
HAVING
    SUM(CASE WHEN BAR.[STATUS]='STAT_A' THEN 1 ELSE 0 END) = 1

score 0 · Answer 3 · answered Dec 19 '12 at 15:35

If it is possible for there to be rows in foo that have no matching rows in bar, you might try something like this:

SELECT
    foo.id AS foo_id,
    NVL
    (
        (
            SELECT
                'TRUE'
            FROM
                bar
            WHERE
                bar.foo_matching_id = foo.id
                AND
                bar.STATUS = 'STAT_A'
                AND
                ROWNUM <= 1
        ),
        'FALSE'
    )
FROM
    foo

There may be more efficient ways to rewrite this query.

SQL Consolidating Multiple rows

3 Answers3