This content has been marked as final. Show 17 replies
1003209 wrote: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.
lets just consider this
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:
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:
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');
This output contains 1 row for every distinct country, whether that country appears ion the country1 column, or in country2, or in both.
CO S_A -- ----------------- AC Some Asymmetrical AD Some Asymmetrical AE Some Asymmetrical AF Some Asymmetrical AG Some Asymmetrical SA All Symmetrical SB All Symmetrical SH All Symmetrical SI All Symmetrical
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:
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.
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 ;
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