Query question

[Darren]

I'm trying to build a query but struggling on how to achieve the
linking.

For examples, I have a table and a view similar to this..

Table: Accounts
ACC_REF Char(3), DESCRIPTION VARCHAR(30), TYPE CHAR(1)

View: Coupons
WEEK_NUM Smallint, ACC_REF Char(3), TOTAL Decimal(10,2)

Linking them on the ACC_REF, I have where clause TYPE='V' from
Accounts and WEEK_NUM = 18 from Coupons. This will produce a list of
accounts only if they have rows in Coupons. I want to get a full list
of the Accounts with Coupon totals or nulls.

Is this possible?

[Dave Hughes]

On Fri, 17 Aug 2007 01:53:39 -0700, Darren scribbled:

I'm trying to build a query but struggling on how to achieve the
linking.

For examples, I have a table and a view similar to this..

Table: Accounts
ACC_REF Char(3), DESCRIPTION VARCHAR(30), TYPE CHAR(1)

View: Coupons
WEEK_NUM Smallint, ACC_REF Char(3), TOTAL Decimal(10,2)

Linking them on the ACC_REF, I have where clause TYPE='V' from Accounts
and WEEK_NUM = 18 from Coupons. This will produce a list of accounts
only if they have rows in Coupons. I want to get a full list of the
Accounts with Coupon totals or nulls.

Is this possible?

You need an "OUTER JOIN" instead of an "INNER JOIN". For example:

SELECT
A.ACC_REF,
A.DESCRIPTION,
A.TYPE,
C.WEEK_NUM,
C.TOTAL
FROM
ACCOUNTS A LEFT OUTER JOIN COUPONS C
ON A.ACC_REF = C.ACC_REF
WHERE
A.TYPE = 'V'
AND C.WEEK_NUM = 18

A *left* outer join is used here as you wish to include all rows from
ACCOUNTS (which is the "left" operand of the join) regardless of whether
there is a corresponding row in COUPONS. Naturally SQL also has a "RIGHT
OUTER JOIN" and "FULL OUTER JOIN" (the latter being when you want to
include all rows from both tables participating in the join regardless of
whether there is a corresponding row in the other table).
HTH,

Dave.

P.S. Could you post the SQL you're attempting to use next time? In this
case it's simple enough that it's no big deal but with more complex
queries it helps enormously to spot errors or just to allow the responder
to edit an existing query without having to write one out from scratch.
Thanks :-)

[Knut Stolze]

Darren wrote:

I'm trying to build a query but struggling on how to achieve the
linking.

For examples, I have a table and a view similar to this..

Table: Accounts
ACC_REF Char(3), DESCRIPTION VARCHAR(30), TYPE CHAR(1)

View: Coupons
WEEK_NUM Smallint, ACC_REF Char(3), TOTAL Decimal(10,2)

Linking them on the ACC_REF, I have where clause TYPE='V' from
Accounts and WEEK_NUM = 18 from Coupons. This will produce a list of
accounts only if they have rows in Coupons. I want to get a full list
of the Accounts with Coupon totals or nulls.

Is this possible?

An OUTER JOIN will do that for you.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Darren]

On 17 Aug, 10:37, Dave Hughes <[e-mail]:

On Fri, 17 Aug 2007 01:53:39 -0700, Darren scribbled:

I'm trying to build a query but struggling on how to achieve the
linking.

For examples, I have a table and a view similar to this..

Table: Accounts
ACC_REF Char(3), DESCRIPTION VARCHAR(30), TYPE CHAR(1)

View: Coupons
WEEK_NUM Smallint, ACC_REF Char(3), TOTAL Decimal(10,2)

Linking them on the ACC_REF, I have where clause TYPE='V' from Accounts
and WEEK_NUM = 18 from Coupons. This will produce a list of accounts
only if they have rows in Coupons. I want to get a full list of the
Accounts with Coupon totals or nulls.

Is this possible?

You need an "OUTER JOIN" instead of an "INNER JOIN". For example:

SELECT
A.ACC_REF,
A.DESCRIPTION,
A.TYPE,
C.WEEK_NUM,
C.TOTAL
FROM
ACCOUNTS A LEFT OUTER JOIN COUPONS C
ON A.ACC_REF = C.ACC_REF
WHERE
A.TYPE = 'V'
AND C.WEEK_NUM = 18

A *left* outer join is used here as you wish to include all rows from
ACCOUNTS (which is the "left" operand of the join) regardless of whether
there is a corresponding row in COUPONS. Naturally SQL also has a "RIGHT
OUTER JOIN" and "FULL OUTER JOIN" (the latter being when you want to
include all rows from both tables participating in the join regardless of
whether there is a corresponding row in the other table).

HTH,

Dave.

P.S. Could you post the SQL you're attempting to use next time? In this
case it's simple enough that it's no big deal but with more complex
queries it helps enormously to spot errors or just to allow the responder
to edit an existing query without having to write one out from scratch.
Thanks :-)

That's the query I have, the issue is for each week I may not have
values for all accounts so when I select where WEEK_NUM = 18 it
reduces the accounts list. For example;

Accounts Table has;

ACC1, Test Account 1, V
ACC2, Test Account 2, V
ACX, Another Account, V

Coupons view returns;
18, ACC1, 100.00
18, ACX, 100.00

What I want to get out is
ACC_REF, DESCRIPTION, TOTAL
ACC1, Test Account 1, 100.00
ACC2, Test Account 2, (null)
ACX, Another Account, 100.00

I want a full list of accounts with totals is available so I can
report all accounts.

Thanks, Darren

[Dave Hughes]

On Fri, 17 Aug 2007 04:00:54 -0700, Darren scribbled:

On 17 Aug, 10:37, Dave Hughes <[e-mail]:

>On Fri, 17 Aug 2007 01:53:39 -0700, Darren scribbled:

I'm trying to build a query but struggling on how to achieve the
linking.

For examples, I have a table and a view similar to this..

Table: Accounts
ACC_REF Char(3), DESCRIPTION VARCHAR(30), TYPE CHAR(1)

View: Coupons
WEEK_NUM Smallint, ACC_REF Char(3), TOTAL Decimal(10,2)

Linking them on the ACC_REF, I have where clause TYPE='V' from
Accounts and WEEK_NUM = 18 from Coupons. This will produce a list of
accounts only if they have rows in Coupons. I want to get a full
list of the Accounts with Coupon totals or nulls.

Is this possible?

You need an "OUTER JOIN" instead of an "INNER JOIN". For example:

SELECT
A.ACC_REF,
A.DESCRIPTION,
A.TYPE,
C.WEEK_NUM,
C.TOTAL
FROM
ACCOUNTS A LEFT OUTER JOIN COUPONS C
ON A.ACC_REF = C.ACC_REF
WHERE
A.TYPE = 'V'
AND C.WEEK_NUM = 18

A *left* outer join is used here as you wish to include all rows from
ACCOUNTS (which is the "left" operand of the join) regardless of
whether there is a corresponding row in COUPONS. Naturally SQL also has
a "RIGHT OUTER JOIN" and "FULL OUTER JOIN" (the latter being when you
want to include all rows from both tables participating in the join
regardless of whether there is a corresponding row in the other table).

HTH,

Dave.

P.S. Could you post the SQL you're attempting to use next time? In this
case it's simple enough that it's no big deal but with more complex
queries it helps enormously to spot errors or just to allow the
responder to edit an existing query without having to write one out
from scratch. Thanks :-)

That's the query I have, the issue is for each week I may not have
values for all accounts so when I select where WEEK_NUM = 18 it reduces
the accounts list. For example;

Accounts Table has;

ACC1, Test Account 1, V
ACC2, Test Account 2, V
ACX, Another Account, V

Coupons view returns;
18, ACC1, 100.00
18, ACX, 100.00

What I want to get out is
ACC_REF, DESCRIPTION, TOTAL
ACC1, Test Account 1, 100.00
ACC2, Test Account 2, (null)
ACX, Another Account, 100.00

I want a full list of accounts with totals is available so I can report
all accounts.

Thanks, Darren

Oh, of course - silly me - I completely overlooked the predicate on the
WEEK_NUM column. There's a couple of ways to fix this. The obvious one is
as follows:

SELECT
A.ACC_REF,
A.DESCRIPTION,
A.TYPE,
C.WEEK_NUM,
C.TOTAL
FROM
ACCOUNTS A LEFT OUTER JOIN COUPONS C
ON A.ACC_REF = C.ACC_REF
WHERE
A.TYPE = 'V'
AND (C.WEEK_NUM = 18 OR C.WEEK_NUM IS NULL)

The less obvious one is to make the condition C.WEEK_NUM = 18 part of the
join condition. This means that the filtering of rows in COUPONS will
occur *prior* to the outer join (as opposed to *after* it which is what
happens with the WHERE clause) which will not exclude non-matching rows
from the ACCOUNTS table due to the nature of the join:

SELECT
A.ACC_REF,
A.DESCRIPTION,
A.TYPE,
C.WEEK_NUM,
C.TOTAL
FROM
ACCOUNTS A LEFT OUTER JOIN COUPONS C
ON A.ACC_REF = C.ACC_REF
AND C.WEEK_NUM = 18
WHERE
A.TYPE = 'V'

(this also serves to illustrate the difference between join conditions
and the WHERE clause - usually not much when you're dealing with INNER
JOINs, but it can matter a great deal with OUTER)
Cheers,

Dave.

[Tonkuma]

Putting all predicates in ON codition would be simpler.
Like this:
------------------------ Commands Entered -------------------------
SELECT
A.ACC_REF
, A.DESCRIPTION
, C.TOTAL
FROM ACCOUNTS A
LEFT OUTER JOIN
COUPONS C
ON A.ACC_REF = C.ACC_REF
AND A.TYPE = 'V'
AND C.WEEK_NUM = 18
;
--------------------------------------------------------------------

ACC_REF DESCRIPTION TOTAL
------- ------------------------------ ------------
ACC1 Test Account 1 100.00
ACC2 Test Account 2 -
ACX Another Account 100.00

3 record(s) selected.

[Brian Tkatch]

On Fri, 17 Aug 2007 01:53:39 -0700, Darren <da********@gmail.com>
wrote:

>I'm trying to build a query but struggling on how to achieve the
linking.

For examples, I have a table and a view similar to this..

Table: Accounts
ACC_REF Char(3), DESCRIPTION VARCHAR(30), TYPE CHAR(1)

View: Coupons
WEEK_NUM Smallint, ACC_REF Char(3), TOTAL Decimal(10,2)

Linking them on the ACC_REF, I have where clause TYPE='V' from
Accounts and WEEK_NUM = 18 from Coupons. This will produce a list of
accounts only if they have rows in Coupons. I want to get a full list
of the Accounts with Coupon totals or nulls.

Is this possible?

A simple correlated subquery would do it. Not as scalable as an OUTER
JOIN, but much clearer.

SELECT
ACC_REF,
(
SELECT
NULLIF(COUNT(*), 0)
FROM
Coupons
WHERE
Coupons.ACC_REF = Accounts.ACC_REF
)
FROM
Accounts
WHERE
TYPE = 'V'

B.

B.