SQLite inner join limit 1

Question

I have two tables objectTable and photo_table. Here's sample data from objectTable

ID  TEXT
1   Kaunas
2   Vilnius
3   Palanga
4   Prienai

and here's data from photo_table

OBJECT_ID PHOTO_ID NAME
1          7       tets7.jpg
1          8       tets8.jpg
1          9       tets9.jpg
1          10      tets10.jpg
1          11      tets11.jpg
2          3       tets3.jpg
2          2       tets2.jpg
3          1       tets1.jpg
3          5       tets5.jpg
4          6       tets6.jpg
4          7       tets7.jpg
4          8       tets8.jpg

So as you can see one object can have many pictuure. I need to get first picture of evety object (My output should be)

ID TEXT   NAME
1 Kaunas  tets7.jpg
2 Vilnius tets3.jpg
3 Palanga tets1.jpg
4 Prienai tets6.jpg

And here's my query:

select * 
from objectTable 
inner join photo_table 
   on photo_table.OBJECT_ID = (select OBJECT_ID 
                               from photo_table 
                               where photo_table.OBJECT_ID = objectTable.ID 
                               order by photo_table.OBJECT_ID desc 
                               limit 1).

However I try to figure almost 3 hours why this query don't work. What I'm missing? Is my logic correct?

Answer 1

Try this way:

SELECT t1.*, t3.NAME
FROM objectTable AS t1
INNER JOIN (
   SELECT OBJECT_ID, MIN(PHOTO_ID) AS PHOTO_ID
   FROM photo_table
   GROUP BY OBJECT_ID
) t2 ON t1.ID = t2.OBJECT_ID
INNER JOIN photo_table AS t3 ON t3.OBJECT_ID = t2.OBJECT_ID AND
                                t3.PHOTO_ID = t2.PHOTO_ID

The trick is to use a derived table that selects the PHOTO_ID value per OBJECT_ID. This value is used in an additional join to photo_table, so as to select the required NAME value.