1 2 Previous Next 17 Replies Latest reply: May 1, 2013 2:40 AM by 1006212 Go to original post RSS
      • 15. Re: Outer Self-Join
        Frank Kulash
        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
        AD 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
          chris227
          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
            1006212
            Thank you so much.
            1 2 Previous Next