Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2013
    Posts
    10

    Unanswered: list country which border both F and D.

    I want to list the country which border both ‘F’ and ‘D’.
    Suppose we have

    INSERT INTO borders VALUES (<Country1>,< Country2>);
    INSERT INTO borders VALUES ('B','F');
    INSERT INTO borders VALUES ('D','B');
    INSERT INTO borders VALUES ('B','NL');
    INSERT INTO borders VALUES ('F','D');

    I write this code:
    Code:
    SELECT DISTINCT Country.Name, borders.Country1, borders.Country2 FROM Country
    INNER JOIN borders
    ON Country.Code = borders.Country1 OR Country.Code = borders.Country2
    WHERE borders.Country1 IN ('F','D') AND borders.Country2 IN('D', 'F')
    ORDER BY Country.Name;
    I expect to see also ‘B’ in result but I just see ‘F’ and ‘D’
    Last edited by OmidUOL; 02-17-13 at 09:45.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by OmidUOL View Post
    I want to list the country which border both ‘F’ and ‘D’.
    Suppose we have

    INSERT INTO borders VALUES (<Country1>,< Country2>);
    INSERT INTO borders VALUES ('B','F');
    INSERT INTO borders VALUES ('B','D');
    INSERT INTO borders VALUES ('B','NL');
    INSERT INTO borders VALUES ('F','D');

    I write this code:
    Code:
    SELECT DISTINCT Country.Name, borders.Country1, borders.Country2 FROM Country
    INNER JOIN borders
    ON Country.Code = borders.Country1 OR Country.Code = borders.Country2
    WHERE borders.Country1 IN ('F','D') AND borders.Country2 IN('D', 'F')
    ORDER BY Country.Name;
    I expect to see also ‘B’ in result but I just see ‘F’ and ‘D
    why?
    you have said you want rows which have a F or D in country1 AND F or D in country 2, which is what you got.

    instead I suspect you want rows which have a F or D in country1 OR F or D in country 2.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2013
    Posts
    10
    I want to list the country which border both ‘F’ and ‘D’.


    INSERT INTO Country VALUES (<Code Of Country>, <Name>)
    INSERT INTO Country VALUES (D, Germany)
    INSERT INTO Country VALUES (F, France)
    INSERT INTO Country VALUES (B, BxBx)


    INSERT INTO borders VALUES ('B','F');
    INSERT INTO borders VALUES ('D','B');
    INSERT INTO borders VALUES ('F','D');
    INSERT INTO borders VALUES ('W','D');



    So how should change the code to see the countries which border both ‘F’ and ‘D’? (in this example B)
    please guide me
    Last edited by OmidUOL; 02-18-13 at 07:12.

  4. #4
    Join Date
    Feb 2013
    Posts
    10
    Is there any idea?
    please help me
    Last edited by OmidUOL; 02-18-13 at 02:48.

  5. #5
    Join Date
    Feb 2013
    Posts
    10
    Is there any solution for this problem?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?
    (Not tested on MySQL.)

    Code:
    SELECT c.Code_Of_Country
         , c.Name
     FROM  Country AS c
     INNER JOIN
           borders AS b
      ON   c.Code_Of_Country
           IN ( b.Country1 , b.Country2 )
     GROUP BY
           c.Code_Of_Country
         , c.Name
     HAVING
           COUNT(
              CASE
              WHEN CASE c.Code_Of_Country
                   WHEN b.Country1 THEN
                        b.Country2
                   WHEN b.Country2 THEN
                        b.Country1
                   END
                   IN ( 'D' , 'F' ) THEN
                   0
              END
           ) = 2
    ;

  7. #7
    Join Date
    Feb 2013
    Posts
    10
    Thank you very much. it works.
    it was very useful

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •