0

I have two MySQL tables: product

product table

photo

photo table

Then I do INNER JOIN 

SELECT * 
FROM product 
INNER JOIN photo 
    ON product.productID = photo.productID

Result of INNER JOIN of these tables

But I need only one raw per unique product ID

What I need

If I try:

SELECT * 
FROM product 
INNER JOIN photo 
    ON product.productID = photo.productID 
ORDER BY product.productID

It returns error

Expression #38 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'photo.photoID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The problem is photoID column. There are few photos per one product, but I need table with only first photo per product.

Can I do it with MYSQL tools like WHERE for example?

Thanks!

Sloan Thrasher
  • 4,953
  • 3
  • 22
  • 40
Sasha Scream
  • 13
  • 2
  • If I try GROUP BY it gives mistake.. MySQL's isn't making a mistake.. You are making the mistake using GROUP BY in a invalid way.. Besides MySQL protected you from invalid results. – Raymond Nijland Apr 15 '18 at 12:49
  • You have **products** and **photos** - But you want the first **meeting**? – Paul Spiegel Apr 15 '18 at 12:52
  • Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) – Paul Spiegel Apr 15 '18 at 12:53
  • 1
    Please define "first meeting". SQL tables represent *unordered* sets. There is no such thing as a "first" row in a table or subset of rows. You need a column that specifies the ordering. – Gordon Linoff Apr 15 '18 at 12:53
  • What is the query that you are using? It seems that you just need `MIN(photoID)` in your SELECT clause. – Paul Spiegel Apr 15 '18 at 12:58
  • I use this SELECT * FROM product INNER JOIN photo ON product.productID=photo.productID – Sasha Scream Apr 15 '18 at 13:00
  • Edit your question and add the query that is returning the error, the one with the group by. – Sloan Thrasher Apr 15 '18 at 13:19
  • Each table should have a unique ID. Why would you allow a product table to have multiple instance of a product ID? – Sloan Thrasher Apr 15 '18 at 13:49

1 Answers1

0

When you use Aggregate function clause,you need to use GROUP BY on no-Aggregate column.

Making a subquery to find the MIN photoID. then JOIN on another subquery

You can try this.

SELECT p.*,T.* FROM 
(
 SELECT productID,MIN(photoID) 'photoID'
 FROM photo
 GROUP BY productID
) T 
INNER JOIN (
 SELECT DISTINCT productID,name
 FROM product
) p ON p.productID = T.productID

sqlfiddle : http://sqlfiddle.com/#!9/97a87d/12

D-Shih
  • 44,943
  • 6
  • 31
  • 51