How can I get a single result from a related table in SQL?

I have the following SQL query:

SELECT 
 gallery.id, 
 gallery.thumbnail_big, 
 products.id, 
 products.title, 
 products.size, 
 products.price, 
 products.text_description, 
 products.main_description 
FROM 
 gallery, 
 products 
WHERE gallery.id=products.id

It gets data from 2 tables - "products" and "gallery". The table "gallery" contains images of the products. Each image has its ID which is equal to the ID of the product. Each product has several images so if I get the data as with the query specified above, I'll get all the images of each product. I need to get 1 image though. 1 image per a product. I know that I ca开发者_开发问答n do it using the DISTINCT keyword. However I can't figure out how that should be used with the query I have.

Since you're using MySQL, I'll give you a MySQL-specific solution that's really easy:

SELECT 
 gallery.id, 
 gallery.thumbnail_big, 
 products.id, 
 products.title, 
 products.size, 
 products.price, 
 products.text_description, 
 products.main_description 
FROM gallery
INNER JOIN products 
ON gallery.id=products.id
GROUP BY products.id

Of course this returns an arbitrary gallery.id and thumbnail_big, but you haven't specified which one you want. In practice, it'll be the one that's stored first physically in the table, but you have little control over this.

The query above is ambiguous, so it wouldn't be allowed by ANSI SQL and most brands of RDBMS. But MySQL allows it (SQLite does too, for what it's worth).

The better solution is to make the query not ambiguous. For instance, if you want to fetch the gallery image that has the highest primary key value:

SELECT 
 g1.id, 
 g1.thumbnail_big, 
 p.id, 
 p.title, 
 p.size, 
 p.price, 
 p.text_description, 
 p.main_description 
FROM products p
INNER JOIN gallery g1 ON p.id = g1.id
LEFT OUTER JOIN gallery g2 ON p.id = g2.id AND g1.pkey < g2.pkey
WHERE g2.id IS NULL

I have to assume you have another column gallery.pkey that is auto-increment, or otherwise serves to uniquely distinguish gallery images for a given product. If you don't have such a column, you need to create one.

Then the query tries to find a row g2 for the same product, that is greater than g1. If no such row exists, then g1 must be the greatest row.

You need to specify which of the values from gallery you want. What you are asking your database to do is to arbitrarily drop records that you specifically told it to select.

What some people will do is add a MAX or MIN to the columns they don't want to duplicate. Everytime I have seen this done it is because they have faulty logic in their query though....

What are you actually trying to select from gallery? If we know that we can probably help write your query.

UPDATE

This method uses apply to only get the first gallery record (ordered by gallery.id) for every product.

SELECT 
 gallery.id, 
 gallery.thumbnail_big, 
 products.id, 
 products.title, 
 products.size, 
 products.price, 
 products.text_description, 
 products.main_description 
FROM  
 products p
OUTER APPLY
        (
        SELECT  TOP 1 g.id, g.thumbnail_big,
        FROM    gallery g
        WHERE   g.id= p.id
        ORDER BY
                g.id DESC
        ) g

Perfect candidate for the group by/having combination.

SELECT 
 gallery.id, 
 gallery.thumbnail_big, 
 products.id, 
 products.title, 
 products.size, 
 products.price, 
 products.text_description, 
 products.main_description 
FROM 
 products,
 gallery 

WHERE gallery.id=products.id
GROUP BY products.id
HAVING MIN(gallery.id)

Will return you all products, one image each. However, the choice for the image was the image having the minimum id.

You don't say that it matters which pic you display, so this is the easiest way to get just one:

SELECT 
 products.id, 
 products.title, 
 products.size, 
 products.price, 
 products.text_description, 
 products.main_description,
 MAX(gallery.thumbnail_big) AS thumbnail_big, 
FROM 
 products,
 gallery
WHERE
 products.id = gallery.id
GROUP BY
 products.id, 
 products.title, 
 products.size, 
 products.price, 
 products.text_description, 
 products.main_description,