join three tables or do nested sql statement in oracle

Question

join three tables or do nested sql in oracle

I have three tables: gut, pos and cfg

gut and pos share a value and a join can be made with gut.gut_val= pos.pos_val.

Similarly the cfg and pos tables share a value and a join can be made with the following: cfg.cfg_dev_cot = pos.pos_dev_val.

However cfg and gut dont share a value. What i want to do is display the values for gut_cou, pos_mon_runch and cfg_cfg_cou, when gut_val = pos_val and cfg_dev_cot = pos_dev_val. Is there a way to do this with joins or is it best to do a nested sql statement?

what i have tried is the following but it throws an oracle error.

select gut.gut_cou, pos.pos_mon_runch, cfg.cfg_cou
from gut,pos,cfg
where gut.gut_val = pos.pos_val
and cfg.cfg_dev_cot = pos.pos_dev_val
and pos.POS_CIT='12345654'
and gut.gut_DAT_DEB <= '08-AUG-11'
and gut.gut_DAT_FIN >= '08-AUG-11'
and gut.gut_TCV ='BOU' 

Solution 1

For a start, get rid of who-ever or what-ever taught you to use , notation for joins. Instead, use something like the following.

This may not get rid of your error message, but you haven't told us what the error is ;)

SELECT
  gut.gut_cou,
  pos.pos_mon_ruch,
  cfg.cfg_cou
FROM
  gut
INNER JOIN
  pos
    ON pos.pos_val = gut.gut_val
INNER JOIN
  cfg
    ON cfg.cfg_dev_cot = pos.pos_dev_val
WHERE
    pos.POS_CIT='12345654'
AND gut.gut_DAT_DEB <= '08-AUG-11'
AND gut.gut_DAT_FIN >= '08-AUG-11'
AND gut.gut_TCV ='BOU'

Solution 2

I'd avoid using

and gut.gut_DAT_DEB <= '08-AUG-11'
and gut.gut_DAT_FIN >= '08-AUG-11'

and go for explicit conversion using a format mask and four digit years

and gut.gut_DAT_DEB <= TO_DATE('08-AUG-2011','DD-MON-YYYY')
and gut.gut_DAT_FIN >= TO_DATE('08-AUG-2011','DD-MON-YYYY')

Also bear in mind that there MIGHT be a time component on the dates so you may wany

and gut.gut_DAT_DEB <= TO_DATE('08-AUG-2011 23:59:59','DD-MON-YYYY HH24:MI:SS')