Select a product by establishment in mysql database!

Asked

Viewed 57 times

1

I have a database with the tables estabelecimentos and produtos. Register the products relating to the establishments.

I need to make a query where select only one product from each establishment, I thought to do with LIMIT but it seems to me that it will not work.

Example: if I have 10 stores and 100 products, I want a consultation that returns me 10 products but being one of each establishment.

Colunas das tabelas

php mysql

  • Add the schema of your tables to the question to facilitate the answer.

  • I added, if you have any more questions just speak. Thank you

  • I think it works with LIMIT, can put in a for indicating the establishment id (or do manual) and searching SELECT * FROM produto WHERE ID_estab = $cont-or-id LIMIT 1

1 answer

3


You can use the group by, would look something like this

SELECT * FROM produto GROUP BY ID_estab

Edit

If you want to display 1 random product from each establishment 

SELECT p.* FROM (SELECT * FROM produto ORDER BY RAND()) as p GROUP BY p.ID_estab
  • 1

    Will it work? No need to specify the column on GROUP BY?

  • In the example you are already specifying GROUP BY ID_estab, that would be the column ID_estab

  • 1

    In this query you need to use the column ID_estab 'cause you’re not doing JOIN to use the column IdEstab table estabelecimento

  • Thanks, it worked perfect, really worth it!

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.