Query question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

usenet@kikobu.com - 22 Jun 2006 14:59 GMT

Hi. I've been struggling with a query, so I'm hoping that I can explain
it in understandable terms.

Given the tables PARENT (id, foo, bar) and CHILD (id, type, parent_id),
I would like to select a result (foo, bar, type) where there's "SELECT
COUNT(distinct type) FROM child" records for each (foo, bar)
combination, ie. type is NULL in the resultset if there's no child of
the parent id that has the given (foo, bar) combination.

So, given the tables:

PARENT
1, X, Y
2, X, Z

CHILD
1, P, 1
1, Q, 2

The result would be:

X, Y, P
X, Y, NULL
X, Z, Q
X, Z, NULL

I've tried building a query with a virtual table containing the
distinct (foo, bar) values and then perform an outer join against that,
but I cannot quite wrap my head around the DB2 outer join syntax (I
found only really simple examples on Google).

This is obviously wrong but shows my intentions:

SELECT vtable.foo, vtable.bar, child.type
FROM (SELECT distinct foo, bar FROM parent) AS vtable, parent, child
WHERE vtable.foo = parent.foo
AND vtable.bar = parent.bar
RIGHT OUTER JOIN parent, child ON parent.id, child.parent_id

Anyone feeling really shard and educational today? :-)

Morten

Reply to this Message

Tonkuma - 22 Jun 2006 16:34 GMT

My idea is followings.

------------------------ Commands Entered ------------------------
CREATE TABLE PARENT
(ID SMALLINT
,foo CHAR(1)
,bar CHAR(1)
);
-------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------ Commands Entered ------------------------
INSERT INTO PARENT
VALUES
(1, 'X', 'Y')
,(2, 'X', 'Z');
--------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------ Commands Entered ------------------------
CREATE TABLE CHILD
(ID SMALLINT
,type CHAR(1)
,ParentID SMALLINT
);
------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------ Commands Entered ------------------------
INSERT INTO CHILD
VALUES
(1, 'P', 1)
,(1, 'Q', 2);
--------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------- Commands Entered -------------------------
SELECT P.foo, P.bar
, C.type
FROM PARENT P
INNER JOIN
(VALUES 1,2 ) P(n)
ON 0=0
LEFT OUTER JOIN
CHILD C
ON P.ID = C.ParentID AND n = 1
ORDER BY
P.foo, P.bar, C.type;;
--------------------------------------------------------------------

FOO BAR TYPE
--- --- ----
X Y P
X Y -
X Z Q
X Z -

4 record(s) selected.

Reply to this Message

usenet@kikobu.com - 23 Jun 2006 09:20 GMT

> My idea is followings.
>
[quoted text clipped - 54 lines]
>
> 4 record(s) selected.

This is really excellent! I solves the problem "good enough" and I
appreciate it a lot. For your information, it does produce extra rows
in the output when a parent has a child of each type, eg.:

INSERT INTO CHILD VALUES (3, 'P', 1)
INSERT INTO CHILD VALUES (4, 'Q', 1)

Results in:

X Y P
X Y Q
X Y -

But for my solution this is perfectly acceptable. Thanks a lot!

Morten

Reply to this Message

mecej4@gmail.com - 22 Jun 2006 16:45 GMT

> Hi. I've been struggling with a query, so I'm hoping that I can explain
> it in understandable terms.
[quoted text clipped - 38 lines]
>
> Morten

How about

select foo,bar,case when p.id=c.parentid then type else null end
as type
from parent p,child c
order by foo,bar,type

If you do not care for the order of the resulting rows, remove the
ORDER clause.

N. Shamsundar
University of Houston

Reply to this Message

usenet@kikobu.com - 23 Jun 2006 09:16 GMT

> > Hi. I've been struggling with a query, so I'm hoping that I can explain
> > it in understandable terms.
[quoted text clipped - 48 lines]
> If you do not care for the order of the resulting rows, remove the
> ORDER clause.

Thanks for the help, but this solution does not solve the problem (it
lacks the joining part).

Br,

Morten

Reply to this Message

Phil Sherman - 26 Jun 2006 18:16 GMT

The following query seems to satisfy your request:

with t1 (type) as
(select distinct type from child)
select parent.foo, parent.bar, child.type
from parent
join t1 on 1 = 1
left outer join child
on child.parentid = parent.id
and child.type = t1.type

If there are a number of types, the following may be preferable:

with t1 (type) as
(select distinct type from child)
select parent.foo, parent.bar, t1.type,
case when child.type = t1.type
then ' Y'
else ' -'
end as "CHILD"
from parent
join t1 on 1 = 1
left outer join child
on child.parentid = parent.id
and child.type = t1.type

The first join is a cartesian join, with one row of output for each
combination of rows from each of the input tables. The left outer join
then gives you the null values where the rows are missing from the child
table. This type of query can require lots of intermediate work space if
there are large numbers of rows of parents and many different values for
the type column of the child table.

Phil Sherman

> Hi. I've been struggling with a query, so I'm hoping that I can explain
> it in understandable terms.
[quoted text clipped - 38 lines]
>
> Morten

Reply to this Message