SQL CROSS JOIN with WHERE CLAUSE not returning anything-CodePudding

I am trying to get a result for the query:

SELECT product, exportcountry
FROM is_exported_to CROSS JOIN is_produced_in
WHERE product = cheese AND origin = 'France';

But I cannot seem to get any return, as a beginner I'm not sure what to try.

The two tables were created as follows:

CREATE TABLE is_produced_in(
  cheese VARCHAR(32),
  origin VARCHAR(32)
  );

  INSERT INTO is_produced_in (cheese, origin)
  VALUES 
  ('Brie', 'France'),
  ('Chrur', 'Tibet'),
  ('Halloumi', 'Greece'),
  ('Orgu', 'Turkey'),
  ('Salers', 'France'),
  ('Serrano', 'Brazil')

CREATE TABLE is_exported_to(
  product VARCHAR(32),
  exportcountry VARCHAR(32)
);

INSERT INTO is_exported_to (product, exportcountry)
VALUES
  ('Brie', 'Greece'),
  ('Halloumi', 'Brazil'),
  ('Halloumi', 'France'),
  ('Halloumi', 'Tibet'),
  ('Orgu', 'France'),
  ('Orgu', 'Tibet'),
  ('Salers', 'Greece'),
  ('Serrano', 'Tibet'),
  ('Serrano', 'Turkey')

Any help would be appreciated, thanks!

CodePudding user response：

You have missed ";" in your init scipt.

CREATE TABLE is_produced_in(
  cheese VARCHAR(32),
  origin VARCHAR(32)
  );

  INSERT INTO is_produced_in (cheese, origin)
  VALUES 
  ('Brie', 'France'),
  ('Chrur', 'Tibet'),
  ('Halloumi', 'Greece'),
  ('Orgu', 'Turkey'),
  ('Salers', 'France'),
  ('Serrano', 'Brazil');

CREATE TABLE is_exported_to(
  product VARCHAR(32),
  exportcountry VARCHAR(32)
);

INSERT INTO is_exported_to (product, exportcountry)
VALUES
  ('Brie', 'Greece'),
  ('Halloumi', 'Brazil'),
  ('Halloumi', 'France'),
  ('Halloumi', 'Tibet'),
  ('Orgu', 'France'),
  ('Orgu', 'Tibet'),
  ('Salers', 'Greece'),
  ('Serrano', 'Tibet'),
  ('Serrano', 'Turkey');

Query sql can be:

SELECT product, exportcountry
FROM is_exported_to CROSS JOIN is_produced_in
ON product = cheese AND origin = 'France';

Use "ON" instead of "WHERE"

see: https://www.db-fiddle.com/f/3jC8PGeZZEuty3XVq8gdzz/2

CodePudding user response：

there is no need to specify join condition in cross join U can use the following query--

select * from is_exported_to CROSS join is_produced_in where origin='France'