How to compare one value, with a value that corresponds to another value?-CodePudding

I have a table:

Table 1
pizza     |price|country|base
-----------------------------------
americano | 2   | U.S   | wholemeal
funghi    | 3   | Italy | wholemeal
sicilliano| 7   | Italy | wholemeal

I need to list all the pizza elements, that have the same country element, as sicilliano pizza, not using a subquery.

CodePudding user response：

INNER JOIN the same table see manual about JOIN

As you don't want subquery, you circumvnt it, by using join, when you run

SELECT t1.*,t2.* FROM Table1 t1 INNER JOIN Table1 t2 ON t1.`country` = t2.`country`

You see tat for ever country row in t1 you find a row from t2. So for siziliano in t1 you get funghi and siziliano in t2.

The rest is reducing all t2 rows, to the ones you need

CREATE TABLE Table1 (
  `pizza` VARCHAR(10),
  `price` INTEGER,
  `country` VARCHAR(5),
  `base` VARCHAR(9)
);

INSERT INTO Table1
  (`pizza`, `price`, `country`, `base`)
VALUES
  ('americano', '2', 'U.S', 'wholemeal'),
  ('funghi', '3', 'Italy', 'wholemeal'),
  ('sicilliano', '7', 'Italy', 'wholemeal');

SELECT t2.`pizza` FROM Table1 t1 INNER JOIN Table1 t2 ON t1.`country` = t2.`country` WHERE t1.`pizza` = 'sicilliano'

| pizza      |
| :--------- |
| funghi     |
| sicilliano |

db<>fiddle here