I've been trying for a few days to build a SQL statement by extracting data from Firebird. I have two dependent tables. The list it obtains is not in the order it should be.
Example tables:
TECHNOLOGY
| ID | NAME |
|---|---|
| 118 | TECHNOLOGY1 |
| 2 | TECHNOLOGY2 |
| 114 | TECHNOLOGY3 |
| 57 | TECHNOLOGY4 |
| 115 | TECHNOLOGY5 |
| 81 | TECHNOLOGY6 |
| 87 | TECHNOLOGY7 |
| 95 | TECHNOLOGY8 |
| 98 | TECHNOLOGY9 |
TECHNOLOGY_INGREDIENTS
| ID | ID_POSITION | NAME |
|---|---|---|
| 1 | 118 | TECHNOLOGY2 |
| 2 | 118 | TECHNOLOGY3 |
| 3 | 118 | ingredient1 |
| 4 | 2 | ingredient21 |
| 5 | 2 | ingerdient22 |
| 6 | 2 | ingredient23 |
| 7 | 114 | TECHNOLOGY4 |
| 8 | 114 | ingredient31 |
| 9 | 57 | ingredient41 |
| 10 | 57 | ingredient42 |
I wrote a SQL query that returns me the right result, but in the wrong order.
My query
select
a.name as name_main,
b.name as name_sub
from
technology a
join technology_ingredients b
on a.id=b.id_position
I get result
| NAME_MAIN | NAME_SUB |
|---|---|
| TECHNOLOGY1 | TECHNOLOGY2 |
| TECHNOLOGY1 | TECHNOLOGY3 |
| TECHNOLOGY1 | ingredient1 |
| TECHNOLOGY2 | ingredient21 |
| TECHNOLOGY2 | ingerdient22 |
| TECHNOLOGY2 | ingredient23 |
| TECHNOLOGY3 | TECHNOLOGY4 |
| TECHNOLOGY3 | ingredient31 |
| TECHNOLOGY4 | ingredient41 |
| TECHNOLOGY4 | ingredient42 |
but I need result like this
| NAME_MAIN | NAME_SUB |
|---|---|
| TECHNOLOGY1 | TECHNOLOGY2 |
| TECHNOLOGY2 | ingredient21 |
| TECHNOLOGY2 | ingerdient22 |
| TECHNOLOGY2 | ingredient23 |
| TECHNOLOGY1 | TECHNOLOGY3 |
| TECHNOLOGY3 | TECHNOLOGY4 |
| TECHNOLOGY4 | ingredient41 |
| TECHNOLOGY4 | ingredient42 |
| TECHNOLOGY1 | ingredient1 |
In the result that I got from my query, you can see that join takes the first record from the first table, then compares it to all the entries in the second table, and so on.
As a result, what I expect, I need a query that will compare the first record from the first table and if the technology name in the second table is uppercase, then the next displayed record shows this technology with its ingredients.
I tried various queries, but unfortunately I failed to get the desired result. Is there a chance to execute such a query with this table structure, or should some more fields appear there?
The most important question is whether I can make a query that will return the result I need. There are no SQL procedures involved, because I display the query result in a ready tool in the application, in which I cannot call the procedure.
I do something like this, but it shows me null. I do not know why:
WITH RECURSIVE technology_upper_name(
id, name) AS (
SELECT a.ID, a.NAME FROM TECHNOLOGY a
UNION ALL
SELECT b.ID_POSITION, b.NAME FROM TECHNOLOGY_INGREDIENTS b
INNER JOIN technology_upper_name tun ON b.ID_POSITION = tun.id
)
SELECT id, name
FROM technology_upper_name