Complex SQL join: group by fails

Question

I have query with left join and groupings.

Query combines 4 tables, and groups by nodes.

EDIT As measure is different based on point, i joined the subquery as z.measure :

and p.start > '1.1.1999'
and p.end > '1.1.1999'

However the left join fails resulting with patterns in data:

A   151 731
A   151 334
B   373 578
B   373 167

The query:

select 

o.value,
count(*) measure ,
z.measure

from point p, hierarchy o, link mo 

left join
(
select 

o.value,
count(*) measure 

from point p, hierarchy o, link mo 

 where 1=1

and
 mo.node=3 and 
    mo.Fault = o.id  and
    mo.Fault2=1 and
    mo.Fault3 = p.id  
 **and p.start > '1.1.1999'**
 group by

 o.value 
) Z  -- group by o.value


-- join parameters


on value = z.value

-- 1st query parameters


 where 1=1

and
 mo.node=3 and 
    mo.Fault = o.id  and
    mo.Fault2=1 and
    mo.Fault3 = p.id  
 and p.end > '1.1.1999'
 group by 

 o.value,
 z.measure
 ;

Is the group by made redundant by the left join ? Should it be outer join?

A left join *is* an outer join. That aside, it's unclear to me what you try to achieve. — GolezTrol, May 27 '15 at 09:02
How does it "fail"? What are you expecting to see? What is your base data? Why are you mixing old-style and ANSI joins in the same query - that'll cause confusion at best. What is the point of joining three tables, then outer-joining to a subquery on the same three tables? And `on value = z.value` doesn't look right; which table/alias is the first `value` supposed to be from? Suspect that will always evaluate to true, anyway. — Alex Poole, May 27 '15 at 09:07
This here `on value = z.value` is ambiguous. Write `on o.value = z.value`. Further, for the `count` aggreagate function, you have to group over the remaining unaggregated fields. — davidhigh, May 27 '15 at 09:07
As o.value = z.value will fail : ORA-00904: "O"."VALUE": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: — user1800552, May 27 '15 at 09:11
@user1800552 - that is because you are mixing the join styles. — Alex Poole, May 27 '15 at 09:15

score 2 · Accepted Answer · edited May 23 '17 at 12:14

Your outer join condition on value = z.value is ambiguous, and is actually effectively doing on z.value = z.value, which is always true (unless the value is null). That is going to give you incorrect results.

You are mixing old-style joins (the comma-separated from list and join conditioned in the where clause) with 'new'-style ANSI joins. The parser evaluates ANSI joins before comma-syntax joins, so it isn't joining things in quite the order you expect. You can tell that is an issue because it isn't currently complaining that the plain value is ambiguous; and because when you try to qualify it as o.value it doesn't know what o means, because of the join order it's having to use.

You need to change all the joins to be ANSI-style; something like:

select 
  o.value,
  count(*) measure,
  z.measure
from point p
join hierarchy o on o.x = p.x
join link mo on mo.Fault = o.id and mo.Fault3 = p.id
left join
(
  select 
    o.value,
    count(*) measure 
    from point p
    join hierarchy o on o.x = p.x
    join link mo on mo.Fault = o.id and mo.Fault3 = p.id
    where mo.node=3
      and mo.Fault2=1
      and p.start_date > date '1999-01-01'
  group by
    o.value 
) z on o.value = z.value
where mo.node=3
  and mo.Fault2=1
  and p.end_date > date '1999-01-01'
group by 
  o.value,
  z.measure
;

I'm not sure how p and o are connected, so I've used 'x' as a common column name; substitute the actual relationship between them. I've also changed the start and end to valid names as those are both reserved, and used date literals rather than relying on your NLS date format setting.

With some made-up data, your original query gets:

V    MEASURE    MEASURE
- ---------- ----------
A        151        731
B        373        731
A        151        334
B        373        334

And this ANSI-join version gets:

V    MEASURE    MEASURE
- ---------- ----------
A        151        731
B        373        334

Hopefully will point you in the right direction at least.

If I've understand the query and relationships, you don't really need a subquery and outer join; you could use a conditional count for both values:

select 
  o.value,
  count(case when p.end_date > date '1999-01-01' then 1 end) as o_measure,
  count(case when p.start_date > date '1999-01-01' then 1 end) as z_measure
from point p
join hierarchy o on o.x = p.x
join link mo on mo.Fault = o.id and mo.Fault3 = p.id
group by 
  o.value
;

V  O_MEASURE  Z_MEASURE
- ---------- ----------
B        373        334
A        151        731

SQL Fiddle.

Complex SQL join: group by fails

1 Answers1