I have a schema where I have images, and I also have results for those images. The results live in N tables, with different schemas. I need to write a search query that can return me all images, and their results, that match some conditions (including limit and offset). 

An image may have 10 results (2 classification, 8 detection). I want the limit to act on the images, and not the results. So, I would expect to get back 10 rows, for 1 image.

Here is what I have so far. The issue with it is the duplication and combining of result rows. I.e. I would expect a row for each result, and not have detection and classification combined like that. Do I need a UNION ALL or something?

```
CREATE TABLE images (
  id         VARCHAR(40)     NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE image_results_classification (
  image_id    VARCHAR(40)          NOT NULL,
  c_confidence  REAL                 NOT NULL,
  FOREIGN KEY (image_id)  REFERENCES images(id)
);

CREATE TABLE image_results_detection (
  image_id    VARCHAR(40)          NOT NULL,
  d_confidence  REAL                 NOT NULL,
  FOREIGN KEY (image_id)  REFERENCES images(id)
);

INSERT INTO images (id) VALUES ('123');
INSERT INTO images (id) VALUES ('456');

INSERT INTO image_results_classification (image_id, c_confidence) VALUES ('123', 0.9);
INSERT INTO image_results_classification (image_id, c_confidence) VALUES ('123', 0.8);
INSERT INTO image_results_classification (image_id, c_confidence) VALUES ('456', 0.7);

INSERT INTO image_results_detection (image_id, d_confidence) VALUES ('123', 0.1);
INSERT INTO image_results_detection (image_id, d_confidence) VALUES ('123', 0.2);
INSERT INTO image_results_detection (image_id, d_confidence) VALUES ('456', 0.3);

```

This schema is contrived for this question to help simplify: there are many more rows on both result tables, and they differ too (not just confidence).

What I want to end up with in my app layer is something like the type:
 Map[Image, (List[ClassificationResult], List[DetectionResult])]

I.e. images, and all there results. **A result set with nulls would be fine.** Perhaps something like this?:

```
id   c_confidence d_confidence
123  0.9          NULL
123  0.8          NULL
123  NULL         0.1
123  NULL         0.2
456  0.7          NULL
456  NULL         0.3

```

This is the query from DB Fiddle:

```
SELECT *
FROM images INNER JOIN
     (SELECT id FROM images LIMIT 10 OFFSET 0
     ) AS i
     ON (images.id = i.id) OUTER LEFT JOIN 
     image_results_classification c
     ON (images.id = c.image_id) OUTER LEFT JOIN 
     image_results_detection d
     ON (images.id = d.image_id);

```

<https://www.db-fiddle.com/f/tuDxwY7kQGfEvZSzaajESG/0>

**Edit: There's a secondary requirement where I need to filter on results, and be able to limit and offset on images.**

I want to be able to perform a query like:

> 
> Give me all images AND all their results, that have a c\_confidence >
>  0.5. I.e. if an image has a c\_confidence of 0.4, then that image (and none of it's results) should be included. If it has a c\_confidence of
>  0.6, then return ALL it's results (including the image\_results\_detection).
> 
> 
> 

I've updated my fiddle to reflect this:
<https://www.db-fiddle.com/f/tuDxwY7kQGfEvZSzaajESG/1>

In the fiddle, I want no results to come back in, because the image does not have an image\_results\_classification with confidence > 0.8