Inner join with first result

In SQL Server, there is two tables: Houses, and their images.

I need a list with 20 houses with the first of their images (only one). I tried:

SELECT top 20 h.id, h.name, im.id, im.name
        FROM image im 
        INNER JOIN house h ON im.house_id = h.id
        WHERE 1=1 AND im.id=(SELECT TOP (1) im2.id FROM image im2 WHERE im.id=im2.id ORDER BY image_code)

but that runs very slowly. There is any way to improve this query?

EDIT:

With the query:

SELECT   h.id, h.name, im.id, im.name  --  What you want to select
FROM     _house h, _image im              --  Tables in join
WHERE    h.id = im.id_house            --  The join (equivalent to inner join)
GROUP BY h.id                          --  This compresses all entries with the
                                       --  same h.id into a single row 
HAVING   im.id =  min(im.id)           --  This is how we select across a group
                                       --  (thus compressing the image table per house)

I'm getting a error message:

_image.id' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Then, I change to:

SELECT   h.id, h.name, im.id, im.name  --  What you want to select
FROM     _house h, _image im              --  Tables in join
WHERE    h.id = im.house_id            --  The join (equivalent to inner join)
GROUP BY h.id,im.id, h.name, im.name                          --  This compresses all entries with the
                                       --  same h.id into a single row 
HAVING   im.id =  min(im.id)

And then I get this result:

How can I take out the repeated values?

EDIT2:

If somebody want to test the queries, this is the script to create the tables and the data that I'm using now (the real data is about 1Million rows):

```
CREATE TABLE _house(
[id] [int] NOT NULL,
[name] varchar NULL
)

CREATE TABLE _image(
[id] [int] NULL,
[name] varchar NULL,
[house_i

You should be using the clause group by

SELECT   h.id, h.name, im.id, im.name  --  What you want to select
FROM     house h,image im              --  Tables in join
WHERE    h.id = im.house_id            --  The join (equivalent to inner join)

GROUP BY h.id                          --  This compresses all entries with the
                                       --  same h.id into a single row 
HAVING   min(im.id)                    --  This is how we select across a group
                                       --  (thus compressing the image table per house)

LIMIT    20;                           --  Selecting the first n values is very
                                       --  DB specific on mysql use the limit clause
                                       --  But I see in your DB it is `top 20`

Note:

Accosding to this page: http://developer.mimer.com/validator/parser200x/index.tml#parser

The having clause is more standard when specified like this (though I can't test this).

HAVING   im.id = min(im.id)

Edit (Based on question Edit).

Your problem is this line:

GROUP BY h.id, im.id, h.name, im.name

This means for every line that is unique across all four values will be compressed together (ie if all four values are the same the lines are compressed together). You need to maintain the original GROUP BY clause (and fix another part of the query).

GROUP BY h.id

I can't test this as I only have MySQL available and you seem to be using an MS product (and my original query worked on MySQL). But based on the error message:


_image.id' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

We don't want to add anything to the GROUP BY clause. Thus following the error message indicates we need to use aggregate functions (in the select probably).

Try changing the select:

SELECT   h.id, h.name, min(im.id), im.name 
                       ^^^^^^^^^^

I am sure if you play around with this you should be able to get it working. Sorry I can not be more exact but that would require using the same product as you.

StackExchange Code Review Q#4952, answer score: 7