SQL/MySQL: get only some columns as distinct in query with joins

Question

In my MySQL database I have the following tables:

recipes (=ricette) which contains the id of the recipe and some basic info

ingredients (=ingredienti) which as got as many rows as many ingredients there are in that recipe, and finally the table

recipe_kind (=tiporicetta) which contains the id of the recipe and the kind of the recipe.

I would like to get all the ingredients of a recipe and so I perform:

SELECT DISTINCT
    r.nome,
    r.descrizione,
    r.persone,
    t.tipo,
    c.cibo,
    i.quantita,
    i.unitamisura_id
FROM
    ricette AS r
        LEFT JOIN
    ingredienti AS i ON i.ricette_id = r.id
        LEFT JOIN
    cibo AS c ON c.id = i.cibo_id
        LEFT JOIN
    tiporicetta AS t ON t.id = r.tiporicetta_id
WHERE
    (r.id = '52')

but with that query I get the name, description, people and kind many times, once for each row. Instead, I would like to have nome, descrizione, persone and tipo only once and then the list of the ingredients. How can I do that with an unique query?

To my way of thinking, and to a rough approximation, there is NO problem in MySQL for which GROUP_CONCAT is the solution. Instead, if you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry
– Strawberry, Commented Mar 16, 2016 at 12:52
You're trying to solve a display issue in SQL. Instead, let your display tier handle it. — Tom H
– Tom H, Commented Mar 16, 2016 at 13:22

Ing. Gerardo Sánchez · Accepted Answer · 2016-03-16 13:31:32Z

1

Try this:

SELECT 
    r.nome,
    r.descrizione,
    r.persone,
    "", "", "", ""
FROM ricette AS r
WHERE 
    (r.id = '52')

UNION

SELECT
    "", "", ""
    t.tipo,
    c.cibo,
    i.quantita,
    i.unitamisura_id
FROM
    ricette AS r LEFT JOIN
    ingredienti AS i ON i.ricette_id = r.id LEFT JOIN
    cibo AS c ON c.id = i.cibo_id LEFT JOIN
    tiporicetta AS t ON t.id = r.tiporicetta_id
WHERE
    (r.id = '52')

answered Mar 16, 2016 at 13:31

Ing. Gerardo Sánchez

1,68515 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL/MySQL: get only some columns as distinct in query with joins

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related