Using a foreach/while/loop with sql query

Question

I'm using the following query to create a view. It's currently only grabbing data from two different tables, subscriptions and subscriptionitems.

For each subscription, I want to grab the item data and output it in the column, the concat function is grabbing one row at the moment and outputting the data in the correct format.

The problem I have is that a subscription can have multiple items, so I need to grab each one and tie it to the correct subscription via the where statement.

How can I do that?

I've read about using UNION ALL, is that the right direction to go?

CREATE VIEW Sub_Products AS  
(
    SELECT 
    i.subscription_id as "Subscription ID",
    concat('product_id:',i.product_id,'|quantity:',i.quantity,'|total:',(i.unit_price * i.quantity),'|meta:|tax:0;') as "Products"  
    FROM  subscriptions s, subscriptionitems i, customerdata c  
    WHERE s.id = i.subscription_id
    AND i.active = 1 
);

So as an example of the output - any with the same subscription id should be combined and the products should be output in the same row.

Sample data from the subscriptionitems table:

id	subscription_id	customer_id	product_id	quantity	active	unit_price
556	230	184	262	1	0	2.79
8100	230	184	262	1	1	2.79
555	230	184	260	1	0	2.52

This is my attempt:

CREATE VIEW Sub_Products AS  
(
    SELECT 
    i.subscription_id as "Subscription ID",
    GROUP_CONCAT('product_id:',i.product_id,'|quantity:',i.quantity,'|total:',(i.unit_price * i.quantity),'|meta:|tax:0;') as "Products"  
    FROM  subscriptions s, subscriptionitems i, customerdata c  
    WHERE s.id = i.subscription_id
    AND i.active = 1 
    GROUP BY i.subscription_id
);

Show: CREATE TABLE for source tables and INSERT INTO with some sample data (both as formatted code, text), desired output for this data. Also provide precise MySQL version. — Akina, Feb 16 '21 at 10:24
@P.Salmon Would that be `LEFT JOIN`? My SQL knowledge isn't great so struggling with this — Rob, Feb 16 '21 at 10:25
update your question add a proper data sample and the expected result — ScaisEdge, Feb 16 '21 at 10:26
`GROUP CONCAT` is useful in this case. Check [this](https://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql) out — Ergis, Feb 16 '21 at 10:56
@Ergis Ok I've given that a go and updated my question with an attempt — Rob, Feb 16 '21 at 11:02

score 1 · Accepted Answer · answered Feb 16 '21 at 12:02

Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.

If you did so, you would probably notice that there is no JOIN condition for customerdata. In fact, that table is not used at all. And neither is subscriptions.

I would suggest

SELECT i.subscription_id ,
       GROUP_CONCAT('product_id:', i.product_id,
                    '|quantity:', i.quantity,
                    '|total:', (i.unit_price * i.quantity),
                    '|meta:|tax:0;'
                   ) as Products  
FROM subscriptionitems i 
WHERE i.active = 1 ;
GROUP BY i.subscription_id;

Note that I fixed the column names so no escaping is needed either.

Using a foreach/while/loop with sql query

1 Answers1