PHP Versions Question posted in
You can find a list of all versions here.

Php versions – MySQL query to extract records based on another table field

I used a little MySQL years ago; now I forgotten a lot and I am lost.
Info: MySQL version 5.7.44 – PHP version 8.1.25
What I want to achieve should be simple: I have a DB with 2 tables [Components and Types in my example); they share the IDtype. This is a simple example:

Tables: 
Components [Code, Qty, IDtype]:
Code10, 2, 1
Code20, 10, 2
Code30, 5, 1
Code40, 3, 2
Code50, 7, 1
---

Types [Type, IDtype]:
Standard, 1
Special, 2
---

I want to obtain this result::

[Standard, 3] Code10, Code30, Code50
[Special, 2] Code20, Code40

Which is: the Type name and the number of retrieved records, followed by the list of extracted records
I tried without success several queries, the last one is:

$query = "SELECT * FROM Components
WHERE  Components.IDtype = ALL (SELECT Types.IDtype FROM Types)";

This does not return anything.

Even this one that I found in another post does not do the job:

SELECT `KEY`, GROUP_CONCAT(Val SEPARATOR ",") as vals
  from table group by `KEY`;

Moreover, admitted that my last query is correct how reported I do not understand how to get the name and record count from the Types table since I only select the IDtype, not the whole record.
Thanks

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks Amit, I tried your query but it gives me an error. This is my query (translated in Italian using the real names) and the error I get:

    SELECT Tipi.Tipo, COUNT(Componenti.Codice) AS Total, GROUP_CONCAT (Componenti.Codice ORDER BY Componenti.Codice SEPARATOR ', ') AS CompCodes FROM Componenti INNER JOIN Tipi ON Componenti.IDtipo = Tipi.IDtipo GROUP BY Tipi.IDtipo
You have an error in your SQL syntax..to use near 'ORDER BY Componenti.Codice SEPARATOR ', ') AS CompCodes FROM Componenti INNER ' at line 2

    Your fiddle works nicely even if there are a couple of points that I do not understand:

    FROM Components c INNER JOIN Types t

    You used c as an alias for Components and t for Types, right? If so, I should write ?

    FROM Componenti Componenti INNER JOIN Tipi Tipi

    Strange syntax and anyway this does not change anything.


  2. 0

    You can use SQL queries with a combination of joins and grouping to aggregate the data from the Components and Types tables based on the IDtype column.

    SELECT t.Type, COUNT(c.Code) AS Total,
    GROUP_CONCAT(c.Code ORDER BY c.Code SEPARATOR ', ') AS Component_Codes
FROM Components c
INNER JOIN Types t ON c.IDtype = t.IDtype
GROUP BY t.Type;

    See this db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search