list country which border both ‘F’ and ‘D’.

OmidUOL · #1 (**permalink**) 02-17-13, 07:15

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’

healdem · #2 (**permalink**) 02-17-13, 07:59

Quote:

Originally Posted by OmidUOL

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.

OmidUOL · #3 (**permalink**) 02-17-13, 08:42

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

OmidUOL · #4 (**permalink**) 02-17-13, 12:53

Is there any idea?
please help me

OmidUOL · #5 (**permalink**) 02-18-13, 06:10

Is there any solution for this problem?

tonkuma · #6 (**permalink**) 02-18-13, 06:55

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
;

OmidUOL · #7 (**permalink**) 02-19-13, 08:01

Thank you very much. it works.
it was very useful

Internet Services

Web Development

Digital Marketing

Consumer Tech