CODEWITHSUNDEEP
sqloracle-databaseselect
Marco Mac
Marco Mac

Reputation: 79

Retrieve a result using two column, or only one of them if the other is null

I'm working on a project and I need to solve this problem. Thank you very much in advance to all of you that can help me.

I have 2 tables 

ARTICLE(id,artNum,artType) 
REQUIREDENGINEERS(id,artNum,artType,reqEngineer)

All the couples (artNum,artType) of REQUIREDENGINEERS are in ARTICLE but not the contrary.

I give the ARTICLE ID as parameter and I need to retrieve the reqEngineer.

The problem is that when I don't have the relative (artNum,artType) in REQUIREDENGINEERS I should use only the artType and take the max(reqEngineer).

I tried but I can't figure it out. Obviously my solution is wrong but maybe something can be saved

select (case 
        when t_art.ARTNUM is null then (select max(g.REQENGINEERS) 
                                        from REQUIREDENGINEERS g 
                                        where g.ARTTYPE= t_dur.ARTTYPE)
                                  else t_dur.REQENGINEER
       end) as required_engineers
FROM 
 ARTICLE t_art,
 REQUIREDENGINEERS t_dur
WHERE 
 :id = t_art.ID AND
 t_art.ARTNUM = t_dur.ARTNUM AND 
 t_art.ARTTYPE = t_dur.ARTTYPE;

Upvotes: 1

Views: 50

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21075

This is probably not the top performing solution, but could be used as a starting point.

Here the sample data

create table ARTICLE as
select 1 id, 1 artNum, 1 artType from dual union all
select 2 id, 2 artNum, 2 artType from dual;

create table REQUIREDENGINEERS as
select  1 id, 1 artNum, 1 artType, 1 reqEngineer from dual union all
select  2 id, 1 artNum, 1 artType, 2 reqEngineer from dual union all
select  3 id, 1 artNum, 2 artType, 1 reqEngineer from dual union all
select  4 id, 1 artNum, 2 artType, 2 reqEngineer from dual;

The query in the WHEREcondition makes simple the same what you requires.

Select all matches an (artNum, artType) or if no match EXISTS use artType but constraint max(reqEngineer)

with art as (select artNum, artType from ARTICLE where id = :id)
select * from REQUIREDENGINEERS r
where   (artNum, artType) in (select artNum, artType from art)   or 
not exists (select null from REQUIREDENGINEERS where  (artNum, artType) 
                 in (select artNum, artType from art)) and
artType in (select   artType from art) and  reqEngineer = 
     (select max(reqEngineer) from REQUIREDENGINEERS where artType = r.artType);

Here the result for 1 - with direct match on both attributes

        ID     ARTNUM    ARTTYPE REQENGINEER
---------- ---------- ---------- -----------
         1          1          1           1 
         2          1          1           2

and 2 - with match on artNum only selecting (all) max REQENGINEER

        ID     ARTNUM    ARTTYPE REQENGINEER
---------- ---------- ---------- -----------
         4          1          2           2

Upvotes: 1

MightyRearranger
MightyRearranger

Reputation: 128

There are potentially a few ways you can go about this, but the easiest way might be to use two separate queries (one for where there is a match on both fields and one for when there isn't) and do a union on the results. Something along the lines of this might work:

SELECT T_DUR.REQENGINEERS
  FROM ARTICLE T_ART
       JOIN REQUIREDENGINEERS T_DUR ON T_ART.ID = T_DUR.ID
                                   AND T_ART.ARTNUM = T_DUR.ARTNUM
                                   AND T_ART.ARTTYPE = T_DUR.ARTTYPE
UNION ALL
SELECT MAX(T_DUR.REQENGINEERS)
  FROM ARTICLE T_ART
       JOIN REQUIREDENGINEERS T_DUR ON T.ART.ID = T_DUR.ID
                                   AND T_ART.ARTTYPE = T_DUR.ARTTYPE
 WHERE T_ART.ARTTYPE NOT IN (SELECT T_ART.ART_TYPE
                               FROM ARTICLE T_ART
                               JOIN REQUIREDENGINEERS T_DUR ON T_ART.ID = T_DUR.ID
                                   AND T_ART.ARTNUM = T_DUR.ARTNUM
                                   AND T_ART.ARTTYPE = T_DUR.ARTTYPE

Upvotes: 0

Related Questions