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

Question

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

`inner join part on man.mid = avail.mid` <-- this join condition looks wrong to me. That being said, 500^4 records is also a lot of records. — Tim Biegeleisen, Sep 22 '22 at 01:58
...then joining them together might result in as many as 500^4 records. — Tim Biegeleisen, Sep 22 '22 at 02:07
Can the time be reduced for example if I only want rows with a particular substring? — Abel, Sep 22 '22 at 02:28
That works, but it seems the second ```where clause``` is not working as I have updated in the question — Abel, Sep 22 '22 at 03:07
It seems ‘part on man.mid = avail.mid’ should perhaps be ‘part on man.mid = part.mid’. Is there a ‘mid’ column on table ‘part’? — Woodchuck, Sep 22 '22 at 04:34

score 1 · Accepted Answer · answered Sep 22 '22 at 09:59

1

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'

answered Sep 22 '22 at 09:59

Paul Vernon

3,818
1
10
23

This produces 0 results – Abel Sep 22 '22 at 11:11
Often the best way to "debug" a SQL query is to cut-it down into a simpler one, then build it up until it "goes wrong". So, for example you could start with `SELECT COUNT(*) FROM avail WHERE MAVAILABLE = 'YES'` then, if that is OK, try `SELECT COUNT(*) FROM man inner join avail on man.mid = avail.mid WHERE MAVAILABLE = 'YES'` etc until you can work out *yourself* where *your* problem lies... – Paul Vernon Sep 22 '22 at 14:13

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

1 Answers1