please help me on this query

It will not work this way. To build such a SQL query each assembly in subassembly table needs to be a separate record i.e.

i think will use find in set function to create such a table and make sum on this table.
i'll post a solution if i find it.

Francesco,

what is the database system you are working on?

I use mysql

with this query

i am able to have the exploded view as admin suggested, with a join on product_table. I only miss a inner join with timesheet to obtain time.

This may be a hack. if there are only 8 part numbers, this may work. Otherwise it would not. Also, this is SQL Server

--Create a View
SELECT ID, [Name], [Assembly],
CASE WHEN [Assembly] LIKE '%X1352%' THEN 4 ELSE 0 END AS X1352_Time, CASE WHEN [Assembly] LIKE '%X3524%' THEN 5 ELSE 0 END AS X3424_Time,
CASE WHEN [Assembly] LIKE '%X7865%' THEN 12 ELSE 0 END AS X7865_Time, CASE WHEN [Assembly] LIKE '%X8976%' THEN 32 ELSE 0 END AS X8976_Time,
CASE WHEN [Assembly] LIKE '%X6777%' THEN 2 ELSE 0 END AS X6777_Time, CASE WHEN [Assembly] LIKE '%X5647%' THEN 54 ELSE 0 END AS X5647_Time,
CASE WHEN [Assembly] LIKE '%X888%' THEN 22 ELSE 0 END AS X888_Time, CASE WHEN [Assembly] LIKE '%X4333%' THEN 9 ELSE 0 END AS X4333_Time
FROM dbo.AssemblyData

--Calculate the view
SELECT Name, [Assembly], X1352_Time + X3424_Time + X7865_Time + X8976_Time + X6777_Time + X5647_Time + X888_Time + X4333_Time AS TotalTime
FROM dbo.AssemblyData_vw

thanks for reply, partnumbers can be a lot more....
anyway the code I posted before works good and i have as result a table:
assembly1 X1352
assembly1 X3524
assembly1 X7865
assembly1 X8976
assembly1 X6777
assembly2 X3524
...............................
where i can do math

I only want to inner join time data from timesheet, but I have difficult with my poor sql capacity. Any advice is appreciated

Hello,

I have explained this situation several times, because we use the multi-value solution, this situation occurs.

In this article I explain this situation in the film relationship and interpreters. https://fhumanes.com/blog/otros-ejemplos/tutorial-curso-basico-de-phprunner/

The solution is to decline a database view in this way:

Regards,
Fernando

P.S. I do not do the Query with the Francesco tables in order to "force" that the solution is perfectly understood.

try this
with recursive temp as (
select name,' ' as partnum, concat(assembly, '-') as assembly, 1 as lev
from subassembly
union all
select name,substring_index(assembly, '-', 1),
substr(assembly, instr(assembly, '-') + 1), lev + 1
from temp
where assembly like '%-%'
)
select temp.name,sum(timesheet.time)
from temp LEFT JOIN timesheet
ON temp.partnum = timesheet.partnumber
where temp.lev > 1
group by temp.name
tables and columns are from your image

thanks for the try I solved with Fernando solution:

I had to put another field workorder to use as filter.
Now I'm able to create a subassembly and retrieve time for it.
I want to make a special thank to Fernando, he is very patient and informative. Maybe the best representative to entice you to buy a license.