CODEWITHSUNDEEP
sql
IsraGab
IsraGab

Reputation: 5185

retrieve id for entry by 2 distinct values

I have the following relation table

image_id type type_id
1 CAMERA aaa
1 OBJECT 123
3 CAMERA ccc
3 OBJECT 123
4 CAMERA aaa
5 CAMERA ddd
5 OBJECT 456
6 CAMERA ddd
6 OBJECT 789

Each image_id has a type CAMERA and some has also type OBJECT. Sometimes the camera has detected an object.

I would like to retrieve all the image_id which their camera has detected an object (sorry fo my poor english) and the same object has been detected at least by 2 distinct cameras.

From the table the output should be: 1,3,4

1 & 3 => because the camera 'aaa' has detected object 123 and the same object has been detected by camera 'ccc' 4 => because it comes from camera 'aaa'

Upvotes: 0

Views: 36

Answers (1)

John K.
John K.

Reputation: 525

Slicker solutions exist, but this is an intuitive approach you can walk through step-by-step:

SELECT 
    image_id
FROM 
    table_name 
WHERE 
    type_id IN (
        SELECT DISTINCT
            type_id 
        FROM 
            table_name 
        WHERE 
            type = 'CAMERA' 
            AND image_id IN (
                SELECT 
                    image_id 
                FROM 
                    table_name 
                WHERE 
                    type_id IN (
                        SELECT 
                            type_id 
                        FROM 
                            table_name 
                        WHERE 
                            type = 'OBJECT' 
                        GROUP BY 
                            type_id 
                        HAVING 
                            COUNT(*) > 1
                        )
                )
        )
;

Figure out which objects have been seen multiple times; figure out which camera type_ids spotted those objects; and return all the image_ids with those type_ids.

Upvotes: 1

Related Questions