How to merge result from two tables 1 - n

Asked

Viewed 4,876 times

1

I have the following problem.

1 table client that has relation with 1 table photos (respective photos from client)

1 client ratio for "n" photos;

In short:

client has id, name.

photo possesses cliente_id, content (bytes).

I can’t do a query that searches all clients but I just want to a photo of each client

I need the select to return this from each client:

OUTCOME THAT I HOPE:

cliente.id, 
cliente.nome, 
foto.conteudo //uma foto para cada cliente.

BECAUSE? I’m making a list of customers that shows only the photo and the name on the homepage.

mysql sql
  • 2

    A photo, but which one? Any one?

  • any of the other table, but it has to be a.

1 answer

3


You can group the result by customer:

SELECT
    cliente.id, cliente.nome, foto.conteudo
FROM cliente
    LEFT JOIN foto /* use INNER JOIN se todo cliente tiver 1+ foto */
    ON foto.cliente_id = cliente.id
GROUP BY cliente.id

  • Perfect, it worked. Can you explain to me why you are only returning a photo ?

  • 2

    When you use GROUP BY, guarantees a row for each item you have grouped (in this case, cliente.id). You can even include COUNT(*) in select to know how many photos you have per client. Only there is no logic for mysql to choose which photo will come. If you have a criterion for this, the query is already more complicated (requires a subquery).

  • Ok missed some Database lessons, thanks for the solution and explanation @bfavaretto.

  • 2

    @Juareza.Francojunior Maybe you didn’t learn this in database class because it’s not a standard SQL behavior but a Mysql trick. Most banks require in GROUP BY all SELECT columns where no aggregation function is used.

Browser other questions tagged mysql sql

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