Parenthese use with and/or clause

Question

I would like to make a sql query than return me the distance between two cities.

SELECT c1.name, c2.name, d.distance
  FROM cities_distance d, city c1, city c2
  WHERE c1.name = d.name_city1
  AND c2.name = d.name_city2
  AND c1.id = c2.id
  AND (c1.name = 'paris'
      AND c2.name = 'berlin')
  or (c1.name = 'berlin'
      AND c2.name = 'paris');

This query return all lines where Paris or Berlin is registered. But in my database I got just 1 line who match with "Paris-Berlin"

My database (cities_distance) :

-----------------------------------
| id | city1  | city2  | distance |
| 1  | berlin | paris  | 1055     |
| 2  | rome   | berlin | 1500     |
-----------------------------------

This seems very likely to be a homework problem. That's fine, but you haven't told us enough about your assignment for us to help you. [How do I ask and answer homework questions?](https://meta.stackoverflow.com/questions/334822/how-do-i-ask-and-answer-homework-questions) — Eric Brandt, Nov 15 '22 at 21:57
Debug qustions require a [mre]. [ask] [Help] Basic questions are faqs. [research effort](https://meta.stackoverflow.com/q/261592/3404097) [“help me"](https://meta.stackoverflow.com/q/284236/3404097) PS Do you understand that the `where` is parsed `(all the unnested ands) or (...)`? https://stackoverflow.com/q/1241142/3404097 — philipxy, Nov 16 '22 at 01:54
I don't think `AND c1.id = c2.id` is the right join condition: that would equal "city 1" and "city 2". Just remove it. This error would have been more obvious with the INNER JOIN syntax — Hans Keﬆing, Nov 16 '22 at 13:49

score 1 · Answer 1 · answered Nov 15 '22 at 23:45

If you do have the city names directly in the cities_distance table, as shown in your sample data, then you can get the result that you want from that table only. Since we can't tell which of the two cities appears in which column, we can search for both tuples with boolean logic:

select name_city1, name_city2, distance
from cities_distance
where (name_city1 = 'paris' and name_city2 = 'berlin') 
   or (name_city2 = 'paris' and name_city1 = 'berlin')

Note: if your database supports tuple equality, the where clause can also be written like so:

where (name_city1, name_city2) in ( ('paris', 'berlin'), ('berlin', 'paris') )

If, on the other hand, you have a different table structure, where the distances table stores forein key references to the primary key of the cities table, then we need to join twice on the cities to filter on the relevant city names:

select c1.name name_city1, c2.name name_city2, cd.distance
from cities_distance cd
inner join cities c1 on c1.id = cd.id_city1
inner join cities c2 on c2.id = cd.id_city2
where (c1.name = 'paris' and c2.name = 'berlin') 
   or (c2.name = 'paris' and c1.name = 'berlin')

score 0 · Answer 2 · answered Nov 15 '22 at 20:35

0

Try this:

SELECT 
    c1.name, 
    c2.name, 
    d.distance
FROM cities_distance d, city c1, city c2
WHERE 
  c1.id = d.id_cityStart
  AND c1.id = c2.id
  AND (
    (c1.name = 'paris'
      AND c2.name = 'berlin')
    OR 
    (c1.name = 'berlin'
      AND c2.name = 'paris')
    );

Also, try running it without the WHERE clause to make sure the dataset looks correct. I'm not sure if d, c1 and c2 are tables or not, you're not joining them on anything.

answered Nov 15 '22 at 20:35

Johnny Bones

8,786
7
52
117

thank for your answer, i have had some parenthese but now i got no result. I have edit my answer : d, c1 and c2 are tables and i joining them with the WHERE and AND (AND c2.name = d.name_city2AND c1.id = c2.id) – user7867717 Nov 15 '22 at 21:05
Please use INNER JOIN, much clearer – Hans Keﬆing Nov 15 '22 at 22:12

Parenthese use with and/or clause

2 Answers2