1 2 Previous Next 17 Replies Latest reply: May 1, 2013 2:40 AM by 1006212 Go to original post
• ###### 15. Re: Outer Self-Join
Hi,
1003209 wrote:
lets just consider this
Okay, so you decided that you can show the problem using 40 rows of sample data, rather than the 100 or more you posted earlier. That's something of an improvement. Now post the exact results you want from those 40 rows.

Is that as simple as you can make the sample data?

Whether of not it's needlessly long, does the sample data really reflect what you're trying to do? It looks like all the relationships in that sample are asymmetrical. Is that a good test? Also, it looks like each country can occur only in country1 or country2, not both. (For example, country1 is sometimes 'ba', but country2 never is;l country2 can be 'hr', but country1 never is.) Is that a good test of what you're trying to do?

Why not use about 10 rows of sample data, such as this:
``````INSERT INTO borders (country1, country2) VALUES ('SA',  'SB');
INSERT INTO borders (country1, country2) VALUES ('SB',  'SA');
INSERT INTO borders (country1, country2) VALUES ('SB',  'AC');
INSERT INTO borders (country1, country2) VALUES ('AC',  'SB');
INSERT INTO borders (country1, country2) VALUES ('AC',  'AD');
INSERT INTO borders (country1, country2) VALUES ('AD',  'AE');
INSERT INTO borders (country1, country2) VALUES ('AE',  'AC');

INSERT INTO borders (country1, country2) VALUES ('AF',  'AG');

INSERT INTO borders (country1, country2) VALUES ('SH',  'SI');
INSERT INTO borders (country1, country2) VALUES ('SI',  'SH');``````
If you have a small set of sample data like this, it's not too har to manually find the correct output, and to post and explain that desired output, like this:
``````CO S_A
-- -----------------
AC Some Asymmetrical
AE Some Asymmetrical
AF Some Asymmetrical
AG Some Asymmetrical
SA All Symmetrical
SB All Symmetrical
SH All Symmetrical
SI All Symmetrical``````
This output contains 1 row for every distinct country, whether that country appears ion the country1 column, or in country2, or in both.
The s_a column says 'All Symmetrical' if that country is only involved in symmetrical relationships. For example, 'SH' and 'SI' are only related to each other, and there is a row with country1='SH' and country2='SI', as well as a row with country1='SI' and country2='SH', so both 'SH' and 'SI' are labled as 'All Symmetrical'.
The s_a column says 'Some Asymmetrical' if that country is involved in any asymmetrical relationships, regardless of whether or not the same country also has symmetrical relationships. For example, there is an asymmetrical relationship between 'AC' and 'AD'. There is a row that has country1='AC' and country2='AD', but no row with country1='AD' and country2='AC', so both 'AC' and 'AD' labelled 'Some Asymmetrical'. It doesn't matter that 'AC' has a symmetrical relationship with 'SB'; 'AC' has at least 1 asymmetrical relationship, so the s_a column for 'AC' will say 'Some Asymmetrical'.

I'm still not sure if that's what you want, but if it is, here's one way to get it:
``````WITH  all_countries     AS
(
SELECT  country1 AS country FROM borders     UNION
SELECT     country2             FROM borders
)
SELECT       country
,       CASE
WHEN  EXISTS (
SELECT  1
FROM          borders  b1
LEFT OUTER JOIN     borders  b2  ON  b2.country1     = b1.country2
AND b2.country2       = b1.country1
WHERE   ac.country     IN (b1.country1, b1.country2)
AND     b2.country1     IS NULL
)
THEN  'Some Asymmetrical'
ELSE  'All Symmetrical'
END     AS s_a
FROM       all_countries  ac
ORDER BY  country
;``````
If you have a separate table that has 1 row per country, then you can use that table in place of the sub-query all_countries.
• ###### 16. Re: Doinng a join
``````with unions as (
select
country1
,country2
from borders
union all
country2 country1
,country1 country2
from borders
)

select
country1
,country2
,case count(*)
when 1 then 'asymetrical'
when 2 then 'symetrical'
else 'undefined'
end sym
from unions
group by
country1
,country2
order by
country1
,country2``````
• ###### 17. Re: Outer Self-Join
Thank you so much.
1 2 Previous Next