P4ul. I am not sure if I follow what you want. But your first example is wrong. You say you have
Code:
select a.Town
, b.Country
from TownCount a
, RefData b
, TownCountry c
where a.Record = 1
and b.Code = c.CountryCode
and c.Record = 2
You don't have Table TownCount defined. Is it supposed to be TownCountry?
Making an assumption it is, you have A.Town that returns London which is fine. But there is no way that B.Country could be Cardiff since B is the RefData table and does not contain Cardiff. If you meant London, England, then I would agree with you.
Code:
WITH TOWNCOUNTRY (RECORD, TOWN, COUNTRYCODE)
AS (
SELECT 1, 'London' , 'A1' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 2, 'Cardiff', 'A2' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 3, 'Hull' , 'A1' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 4, 'Luton' , 'A2' FROM SYSIBM.SYSDUMMY1
)
, REFDATA (TYPE, CODE, COUNTRY)
AS (
SELECT 'Country', 'A1','England' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 'Country', 'A2','Wales' FROM SYSIBM.SYSDUMMY1
)
SELECT A.TOWN, B.COUNTRY
FROM TOWNCOUNTRY A
, REFDATA B
, TOWNCOUNTRY C
WHERE A.RECORD = 1
AND B.CODE = C.COUNTRYCODE
AND C.RECORD = 2
;
TOWN COUNTRY
------- -------
London England
The Reason you get 0 rows when you change the CountryCode to A3, which isn't in the table is that part of the Query returns 0 rows and Inner Joins can not match to something that doesn't exist so none of the Joins would return rows either.
What you probably want is a Left Outer Join. But without knowing exactly what you want, I am not sure I can show you what to do.
For example, if you just want to return the Town from TownCountry and the Country from RefCode, why are you involving 3 tables?
Code:
SELECT A.TOWN, B.COUNTRY
FROM TOWNCOUNTRY A
, REFDATA B
WHERE A.RECORD = 1
AND A.COUNTRYCODE = B.CODE
TOWN COUNTRY
------- -------
London England
This Query doesn't care what the Code is for Wales is (as long as it isn't the same A1 used by England).
If you want a row returned, even if a Country Code doesn't exist, use a left outer join. Changing London CountryCode to A3 you get:
Code:
WITH TOWNCOUNTRY (RECORD, TOWN, COUNTRYCODE)
AS (
SELECT 1, 'London' , 'A3' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 2, 'Cardiff', 'A2' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 3, 'Hull' , 'A1' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 4, 'Luton' , 'A2' FROM SYSIBM.SYSDUMMY1
)
, REFDATA (TYPE, CODE, COUNTRY)
AS (
SELECT 'Country', 'A1','England' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 'Country', 'A2','Wales' FROM SYSIBM.SYSDUMMY1
)
SELECT A.TOWN, B.COUNTRY
FROM
(SELECT TOWN, COUNTRYCODE
FROM TOWNCOUNTRY
WHERE RECORD = 1
) AS A
LEFT OUTER JOIN
REFDATA B
ON A.COUNTRYCODE = B.CODE
;
TOWN COUNTRY
------- -------
London -
dBforums
>
SELECT CASE when no rows
07-16-11, 18:10
Linear Mode
