[SOLVED] Retrieving SQLite data of one table from multiple values in a specific column of another table

Retrieving SQLite data of one table from multiple values in a specific column of another table

I have 2 tables in SQLite (one containing Image Meta Data and other containing the geographical region where the image is captured). Both these table contain "ImageName" as a unique feature/key.

Now, I need to fetch Image metadata for a specific combination of regions. I have a query something like below,

SELECT * FROM IMG_META where ImageName = (SELECT ImageName from IMG_REGION where Country = 'denmark' or Country = 'Germany')

On querying second select statement alone, it returns 7 Image names (1 from w.r.t and 6 w.r.t Germany). But, when the same query is used as mentioned below, I am not able to fetch any data.

I am aware that the query would not work as I'm trying to provide 7 value to a "ImageName" of first select statement as the query expects single "ImageName". Query works when I fetch single Image name from second select statement.

I need to fetch Image Meta-data (First table data) for all 7 Image names which is returned from second select statement.

Any approach to achieve this goal?

Solution

To get matching data for two table use join:

SELECT * FROM IMG_META 
JOIN IMG_REGION ON IMG_META.ImageName=IMG_REGION.ImageName
WHERE IMG_REGION.Country = 'denmark' or IMG_REGION.Country = 'Germany'