0

Summary: I have a query that currently gives me N rows, depending on the value given in WHERE clause. These rows may contain a certain value in a specific column (container_id from a different table). If the value is > 0, I want to repeat the query with the value as the new WHERE clause and append the result to the table. This should be repeated until the value is = 0 (or NULL).

What I've already tried: I tried to expand the WHERE clause with an OR / AND statement

WHERE 
     c.id = 55 OR
     c.id = i.container_id;

but this gives me more or no results than expected.

SELECT DISTINCT
    c.*, 
    container_items.slot, 
    items.id AS itemid, 
    items.item_template_id,
    items.container_id,
    items.quantity
FROM 
    items i,
    containers c 
LEFT JOIN 
    container_items ON container_items.containerid = c.id 
LEFT JOIN 
    items ON items.id = container_items.itemid 
WHERE 
     c.id = 55;

The upper query gives me the following result:

id |   Name   |  x  |  x  |  container_id | quantity
--------------------------------------------------------
55 | Bin      |  x  |  x  |      63       |    1
55 | Bin      |  x  |  x  |       0       |    6

I now want to append the rows, that would be given, if I ran query again with c.id = 63:

id |   Name   |  x  |  x  |  container_id | quantity
--------------------------------------------------------
63 | Basket   |  x  |  x  |      67       |    1
63 | Basket   |  x  |  x  |       0       |    6

Since the next result also contains container_id > 0, I want to repeat and append the rows, etc. until no further rows can be appended, because of an invalid or 0 value:

id |   Name   |  x  |  x  |  container_id | quantity
--------------------------------------------------------
67 | Basket   |  x  |  x  |       0       |    1
67 | Basket   |  x  |  x  |       NULL    |    6

So the expected result would the combined from above:

id |   Name   |  x  |  x  |  container_id | quantity
--------------------------------------------------------
55 | Bin      |  x  |  x  |      63       |    1
55 | Bin      |  x  |  x  |       0       |    6
63 | Basket   |  x  |  x  |      67       |    1
63 | Basket   |  x  |  x  |       0       |    6
67 | Basket   |  x  |  x  |       0       |    1
67 | Basket   |  x  |  x  |       NULL    |    6

I think I'm missing a major keyword in MySQL to do this. All links would be helpful :)

Kiwi
  • 38
  • 5
  • 1
    You can create a stored procedure and embed your logic there with for/while loops and query cursors if needed. Here's an example: https://stackoverflow.com/questions/5125096/for-loop-example-in-mysql But then embedding your business logic into a stored procedure is usually not a good idea. – Tim May 02 '19 at 16:18
  • 1
    The keyword you may be looking for is [recursive cte](https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive). It requires at least MySQL 8. Unless I missed something, your problem is a (standard) hierarchical query (a container containing other containers or end-points). You can find solutions to this problem (with or without MySQL 8) in (e.g.) [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/q/20215744). – Solarflare May 02 '19 at 17:16
  • Thank you both. Recursive queries was the keyword I was looking for. I'll try both approaches (for loop and recursive). – Kiwi May 03 '19 at 13:58

0 Answers0