ITzBrotasco ITzBrotasco - 26 days ago 19
MySQL Question

Join Select Statement after on

Is it possible to join on a part of a string that i split up?
I tried something:

SELECT param.par_name,
PARPAT.pp_pattern,
KOM.kom_name
FROM inv.inv_parpat PARPAT
INNER JOIN inv.inv_param PARAM
ON PARPAT.pp_par_id = PARAM.par_id
INNER JOIN inv.inv_komponente KOM
ON KOM.kom_id = (SELECT Substr(PARPAT.pp_pattern,
Instr(PARPAT.pp_pattern, ':') + 1
)
FROM inv.inv_parpat)
WHERE PARPAT.pp_pattern LIKE '%ATA%';


This statement says:
Single row subquery returns more than one row

Answer

Why do you use a sub query? You already have this table, why not use it? :

SELECT param.PAR_NAME,
       PARPAT.PP_PATTERN,
       KOM.KOM_NAME
  FROM INV.INV_PARPAT PARPAT
       INNER JOIN INV.INV_PARAM PARAM 
        ON PARPAT.PP_PAR_ID = PARAM.PAR_ID
       INNER JOIN INV.INV_KOMPONENTE KOM
        on KOM.KOM_ID = SUBSTR(PARPAT.PP_PATTERN,INSTR(PARPAT.PP_PATTERN, ':')+1)
WHERE PARPAT.PP_PATTERN LIKE '%ATA%';
Comments