SQL cross join with partial table

user_14859906 0
Viewed 216

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
sql-insert cross-join postgresql sql python
1 Answers

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.

user_104988280
Related