Question

0

246

Views

SQL cross join with partial table

I am struggling to get the following SQL query to work:

sql_query = f'''
    INSERT INTO table3(animal_id, fruit_id)
    SELECT table1.id, table2.id
    FROM table1
    CROSS JOIN table2
    WHERE table2.id = "{some_value}";
'''

Basically I want to append to table3 the cross join of some columns from table 1 and table 2, but to limit the joined values from table 2 to those rows where table2.id equals some spefic value. Does anyone know what I'm doing wrong here?

EDIT: so say you had the following three tables:

1
ID ANIMAL
  1    Cat
  2    Dog

2
ID  FRUIT
  1  apple
  2 orange
  3 banana

3
ID ANIMAL  FRUIT
  1   Bird mango
  2   Bird grape

How can I make it that I cross join the animal and fruit columns of tables 1 and 2 and append the result to table 3 but only for those rows of table 1 which equal the value specified in the curly brackets, e.g. here 'Dog'

3
ID ANIMAL   FRUIT
  1   Bird  mango
  2   Bird  grape
  3    Dog  apple
  4    Dog orange
  5    Dog banana

over 4 years ago · Santiago Trujillo

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-05-12T16:53:41.593Z

Your code should work if you use single instead of double quotes for the parameter that you pass, but you don't need the CROSS join if you are sure that table2 contains that parameter .
You can simplify the code to:

INSERT INTO table3 (ANIMAL, FRUIT) 
SELECT ANIMAL, 'Dog'
FROM table1;

See the demo.

0

246

SQL cross join with partial table

Basically I want to append to table3 the cross join of some columns from table 1 and table 2, but to limit the joined values from table 2 to those rows where table2.id equals some spefic value. Does anyone know what I'm doing wrong here?

1 answers

0

Find remote jobs

Andres GPT