CODEWITHSUNDEEP
sqldb2db2-zos
Abel
Abel

Reputation: 113

db2 sql query on 4 tables not running? (taking too long?)

I have 4 tables with columns as illustrated below Table1: PART with the following columns | PID | PCODE| PNAME| MID|

Table2: MAN with the following columns | MID | MCODE| MNAME |MVALID|

Table3: LVL with the following columns. |PID | QUANTITY|

Table4: AVAIL with the following columns |MID | MAVAILABLE|

I want the query output in this form <PCODE>,<MCODE>,<QUANTITY>,<MNAME> so I tried the following sql:

select 
    part.pcode,
    man.mcode,
    lvl.quantity,
    man.mname
    from man
    inner join avail on man.mid = avail.mid
    inner join part on man.mid = avail.mid
    inner join lvl on part.pid = lvl.pid 
    where PNAME like '%phyll%'
        and MAVAILABLE = 'YES'

However when I execute it takes forever that I end up terminating, Also the max rows of the tables is 500. Is there anywhere I am making an error?

Edit: New question: using a WHERE clause (with this statement only, where PNAME like '%phyll%' reduces execution time and works, however adding the and MAVAILABLE = 'YES' to filter MAVAILABLE rows that contain the word YES produces 0 results but there are a number of results with YES in the columns. Is there any error here? the MAVAILABLE column containts either YES or NO strings only

Upvotes: 0

Views: 69

Answers (1)

Paul Vernon
Paul Vernon

Reputation: 3901

Try

select 
   part.pcode,
   man.mcode,
   lvl.quantity,
   man.mname
from man
inner join avail on man.mid = avail.mid
inner join part  on man.mid = part.mid     -- I made a change here
inner join lvl   on part.pid = lvl.pid 
where PNAME like '%phyll%'
    and MAVAILABLE = 'YES'

Upvotes: 1

Related Questions