SQL get same values with different id-CodePudding

Imagine I have the following tables in SQLite:

colors:

id INTEGER primary key
name TEXT
brightnes INTEGER

painting:

id INTEGER primary key
name TEXT
colors TEXT
material TEXT

paint:

color_id foreign key
painting_id foreign key

Now I would like to select all the paintings where the color red and green was used. How could I do this? I thought about something like:

SELECT name
FROM painting 
WHERE colors.name = 'Green' 
  AND colors.name = 'Red' 
  AND colors.id = paint.color_id 
  AND paint.painting_id = painting.id;

Unfortunately, this is not returning what I would like to get. I read a lot about GROUP BY and HAVING but can't set my mind around this.

Could someone please help?

Thanks!

CodePudding user response：

How is the connection made between the two tables? You should have primary and foreign key if you have two separate tables.. Than you should use inner join to join the tables and get the results you want.. Also you should use where clause Exmp:

select * from painting inner join colors on colorsFkey = colorsId
where colors.name = 'red' OR colors.name = 'green'

Try also learning and and or and also table connection with primary and foreign key

CodePudding user response：

is color in painting table a foreign key ? !!!

CodePudding user response：

color_id painting_id are both foreign key. I've tried really all of them. inner and outer JOINT and so on. Nothing worked