Complexe sql query from multiple tables

Question

I have a problem with my query: So, I have a table call supplier:

ref_article    supplier_id
1903           10

I have another table call gift:

id_gift        etat          id_adresse       ref_article
100455         3               1              1903
100456         3               2              1903
100457         3               3              1903

And I have the table gift_adresse:

id             name           surname
1               jkkjl         hkj
2               hjhjk         jklj
3               kjkj          hjjkhk

My query is like this:

SELECT
supp.ref_article,
COUNT(g.id_instant_gagnant) AS gifts_number
FROM supplier supp
LEFT JOIN gift g ON supp.ref_article = g.ref_article
INNER JOIN gift_adresse g_adr ON g.id_adresse = g_adr.id_adresse
WHERE supp.supplier_id = 10 AND g_ig.etat = 3
GROUP BY g.ref_article

For this query I get:

ref_article    gifts_number
1903           3

The problem is that I want to get all id_gift for this ref_article for my example : 100455,100456,100457 Is it possible to do in a single query?

mysql <> sql server. which one are you using

ughai
– ughai

2015-07-06 10:47:02 +00:00
Commented Jul 6, 2015 at 10:47 — ughai
– ughai, Commented Jul 6, 2015 at 10:47

Ravinder Reddy · Accepted Answer · 2015-07-06 10:49:25Z

1

You can use GROUP_CONCAT(expr) for the desired CSV output.

MySQL Solution:

SELECT
       supp.ref_article
     , COUNT(g.id_instant_gagnant) AS gifts_number
     , GROUP_CONCAT(g.id_gift) AS gift_ids
  FROM supplier supp
  LEFT JOIN gift g ON supp.ref_article = g.ref_article
       INNER JOIN gift_adresse g_adr ON g.id_adresse = g_adr.id_adresse
 WHERE supp.supplier_id = 10 
   AND g_ig.etat = 3
 GROUP BY g.ref_article

answered Jul 6, 2015 at 10:49

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2015-07-06 10:55:48Z

The answer to your direct question is group_concat(). However, your query is rather awkward:

SELECT supp.ref_article,
      COUNT(g.id_instant_gagnant) AS gifts_number
FROM supplier supp LEFT JOIN
-------------------^ LEFT JOIN is turned to inner join
     gift g
     ON supp.ref_article = g.ref_article INNER JOIN
     gift_adresse g_adr
     ON g.id_adresse = g_adr.id_adresse
--------^ by this expression here
WHERE supp.supplier_id = 10 AND g_ig.etat = 3
--------------------------------^ I don't know what this is
GROUP BY g.ref_article
---------^ Don't aggregate by a `LEFT JOIN`ed table unless you want `NULL` values *AND* this is not the expression in the `FROM`

I would suggest dispensing with the LEFT JOIN, fixing the references in the table, and removing the third table:

SELECT supp.ref_article,
       COUNT(g.id_instant_gagnant) AS gifts_number,
       GROUP_CONCAT(g.id_gift) as gifts
FROM supplier supp JOIN
     gift g
     ON supp.ref_article = g.ref_article and g.etat = 3
WHERE supp.supplier_id = 10 
GROUP BY supp.ref_article

If you want a LEFT JOIN, you can now add it back in. The condition on etat is in the ON clause, where it would need to be.

Collectives™ on Stack Overflow

Complexe sql query from multiple tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related