Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2010
    Posts
    5

    Unanswered: nested join instead of union?

    I've been googling for a week and have learned a lot about sql but cannot solve my problem

    basically here's what i want:

    all rows in t2wrwrh with master_wo_nbr = 1551785
    and
    all rows in t2wowoh with master_wo_nbr = 1551785
    and
    all rows in t2msmsh with wo_nbr = to wo_nbr in t2wowoh

    the same wo_nbr can be in all three tables

    i've tried a full outer join on the first two tables, but it takes about an hour to execute.

    the query i have so far (below) only takes a minute to execute but i have duplicate rows resulting where placeholders for the union are used and the wo_nbr exists in all three tables. I couldn't get a nested join to work, which seems like what i need...

    Code:
    select 	coalesce(a.wo_nbr, '0000000') as wo_nbr,
    		coalesce(a.wkr_nbr, '0000000') as wkr_nbr, 
    		coalesce(a.wo_desc, ' ') as wo_desc,
    		coalesce(a.perf_dept, ' ') as wo_perf_dept,
    		coalesce(a.wo_status, ' ') as wo_status,
    		coalesce(a.wo_stat_date, '0001-01-01') as wo_stat_date,
    		coalesce(a.est_tot_cost, 0) as est_tot_cost,
    		coalesce(a.tot_cost, 0) as tot_cost,
    		coalesce(a.est_labour, 0) as est_labour,
    		coalesce(a.labour_cost, 0) as labour_cost,
    		coalesce(a.est_dir_matl, 0) as est_dir_matl,
    		coalesce(a.dir_matl_cost, 0) as dir_matl_cost,
    		coalesce(a.est_stores, 0) as est_stores,
    		coalesce(a.stores_cost, 0) as stores_cost,
    		coalesce(a.est_other_chg, 0) as est_other_chg,
    		coalesce(a.other_chg, 0) as other_chg, 
    		coalesce(a.est_manhrs, 0) as est_manhrs,
    		coalesce(a.manhrs, 0) as manhrs,
    	
    		coalesce(b.wkr_nbr, '0000000') as wkr_nbr,
    		coalesce(b.wo_nbr, '0000000')as wo_nbr,
    		coalesce(b.wkr_desc, ' ') as wkr_desc,
    		coalesce(b.rqst_dept, ' ') as rqst_dept,
    		coalesce(b.perf_dept, ' ') as perf_dept,
    		coalesce(b.wkr_status, ' ') as wkr_status,
    		coalesce(b.wkr_stat_date, '0001-01-01') as wkr_stat_date,
    		coalesce(b.est_plan_hrs, 0)as est_plan_hrs,
    
    		'0000000' as msr_nbr,
    		' ' as msr_status,
    		' ' as msr_purpose,
    		0 as msr_est_value,
    		'0000000' as wkr_nbr,
    		'0000000' as wo_nbr,
    		'00' as wo_task
    		
    from ats.t2wrwrh b left outer join ats.t2wowoh a
    on		b.wo_nbr = a.wo_nbr
    where b.master_wo_nbr = '1551785'
    
    union
    
    select 	a.wo_nbr,
    		a.wkr_nbr,
    		a.wo_desc,
    		a.perf_dept,
    		a.wo_status,
    		a.wo_stat_date,
    		a.est_tot_cost,
    		a.tot_cost,
    		a.est_labour,
    		a.labour_cost,
    		a.est_dir_matl,
    		a.dir_matl_cost,
    		a.est_stores,
    		a.stores_cost,
    		a.est_other_chg,
    		a.other_chg,
    		a.est_manhrs,
    		a.manhrs,
    
    		'0000000',
    		'0000000',
    		' ',
    		' ',
    		' ',
    		' ',
    		'0001-01-01',
    		0,
    
    		coalesce(c.msr_nbr, '0000000'),
    		coalesce(c.msr_status, ' '),
    		coalesce(c.msr_purpose, ' '),
    		coalesce(c.msr_est_value, 0),
    		coalesce(c.wkr_nbr, '0000000'),
    		coalesce(c.wo_nbr, '0000000'),
    		coalesce(c.wo_task, '00')
    
    from ats.t2wowoh a left outer join ats.t2msmsh c
    on		a.wo_nbr = c.wo_nbr
    where a.master_wo_nbr = '1551785'
    
    order by 1,2,3
    perhaps there's a simple concept i'm missing as a self taught programmer...

    if you could help it would be very appreciated...

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS are you using? Can you give an example of what you have in the tables and what you expect to get as a result of the query?

    Andy

  3. #3
    Join Date
    Mar 2010
    Posts
    5
    I don't know the version of DB2, I believe it's running on Server 2008.

    here is what i'm getting (with the unimportant columns removed)

    Code:
    WO_NBR	WKR_NBR	WKR_NBR	WO_NBR	MSR_NBR
    1532493	0000000	0000000	0000000	0000000
    1532555	0000000	0000000	0000000	0000000
    1539839	0291539	0000000	0000000	02653120
    1539839	0291539	0291539	1539839	0000000
    1546929	0000000	0000000	0000000	02568270
    1549916	0292982	0000000	0000000	0000000
    1549916	0292982	0292982	1549916	0000000
    1549939	0292983	0000000	0000000	0000000
    1549939	0292983	0292983	1549939	0000000
    1549942	0292984	0000000	0000000	0000000
    1549942	0292984	0292984	1549942	0000000
    1549945	0292989	0000000	0000000	0000000
    1549945	0292989	0292989	1549945	0000000
    1549946	0292990	0000000	0000000	0000000
    1549946	0292990	0292990	1549946	0000000
    1549948	0292991	0000000	0000000	0000000
    1549948	0292991	0292991	1549948	0000000
    1549951	0292992	0000000	0000000	0000000
    1549951	0292992	0292992	1549951	0000000
    1551768	0000000	0000000	0000000	0000000
    1559486	0000000	0000000	0000000	0000000
    Here's what I would like

    Code:
    WO_NBR	WKR_NBR	WKR_NBR	WO_NBR	MSR_NBR
    1532493	0000000	0000000	0000000	0000000
    1532555	0000000	0000000	0000000	0000000
    1539839	0291539	0291539	1539839	02653120
    1546929	0000000	0000000	0000000	02568270
    1549916	0292982	0292982	1549916	0000000
    1549939	0292983	0292983	1549939	0000000
    1549942	0292984	0292984	1549942	0000000
    1549945	0292989	0292989	1549945	0000000
    1549946	0292990	0292990	1549946	0000000
    1549948	0292991	0292991	1549948	0000000
    1549951	0292992	0292992	1549951	0000000
    1551768	0000000	0000000	0000000	0000000
    1559486	0000000	0000000	0000000	0000000
    the row where wo_nbr is 1539839 is the best example of the duplicate I referred to.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    OK, I see where you want to get to, but where are you coming from? How about a sample of the data in the tables you are querying?

    Andy

  5. #5
    Join Date
    Mar 2010
    Posts
    5
    Well at a basic level

    t2wrwrh
    keyed on wkr_nbr
    may or may not have wo_nbr value
    may or may not have master_wo_nbr value
    does not have msr_nbr
    Code:
    WKR_NO     WO_NBR     MASTER _WO NBR 
    0291539	1539839	1551785
    0291656	1651369	1551785
    0291657	1609503	1551785
    0292045	0000000	1551785
    0292047	1579197	1551785
    0292048	0000000	1551785
    0292050	1579203	1551785
    0292057	1579200	1551785
    0292077	1583240	1551785
    0292763	1575796	1551785
    0292764	1575794	1551785
    0292982	1549916	1551785
    0292983	1549939	1551785
    0292984	1549942	1551785
    0292989	1549945	1551785
    0292990	1549946	1551785
    0292991	1549948	1551785
    0292992	1549951	1551785
    0293018	1568471	1551785
    0293120	1575137	1551785
    0293248	0000000	1551785

    t2wowoh
    keyed on wo_nbr
    may or may not have wkr_nbr value
    may or may not have master_wo_nbr
    may or may not have msr_nbr value
    Code:
    WO_NBR     WKR_NBR   MASTER_WO_NBR
    1532493	0000000	1551785
    1532555	0000000	1551785
    1539839	0291539	1551785
    1546929	0000000	1551785
    1549916	0292982	1551785
    1549939	0292983	1551785
    1549942	0292984	1551785
    1549945	0292989	1551785
    1549946	0292990	1551785
    1549948	0292991	1551785
    1549951	0292992	1551785
    1551768	0000000	1551785
    1559486	0000000	1551785
    1565582	0000000	1551785
    1568471	0293018	1551785
    1571148	0295701	1551785
    1575135	0295907	1551785
    1575137	0293120	1551785
    1575785	0291498	1551785
    1575789	0291095	1551785
    1575793	0291160	1551785

    t2msmh
    keyed on msr_nbr
    may or may not have wo_nbr value
    may or may not have wkr_nbr value
    does not have master_wo_nbr
    Code:
    MSR_NBR    WO_NBR    WKR_NBR
    00384931	0038947	0000000
    00384941	0035857	
    00384986	0038991	0000000
    00385000	0036436	
    00385012	0038996	
    00385013	0038997	
    00385030	0035786	
    00385124	0037631	
    00385127	0039010	
    00385156	0039013	
    00385157	0039014	
    00385169	0039017	
    00385184	0039018	
    00385188	0039019	
    00385191	0039020	
    00385196	0038998	
    00385213	0038637	
    00385218	0038642	
    00385222	0038639	
    00385227	0038640

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think I see what you want. Do not use a UNION, that is giving you the duplicates. For your result set, where is each column coming from?

    Andy

  7. #7
    Join Date
    Mar 2010
    Posts
    5
    Code:
    +---t2wowoh----+----t2wrwrh----+-t2msmsh-+
    WO_NBR	WKR_NBR	WKR_NBR	WO_NBR	MSR_NBR
    1532493	0000000	0000000	0000000	0000000
    1532555	0000000	0000000	0000000	0000000
    1539839	0291539	0291539	1539839	02653120
    1546929	0000000	0000000	0000000	02568270
    1549916	0292982	0292982	1549916	0000000
    1549939	0292983	0292983	1549939	0000000
    1549942	0292984	0292984	1549942	0000000
    1549945	0292989	0292989	1549945	0000000
    1549946	0292990	0292990	1549946	0000000
    1549948	0292991	0292991	1549948	0000000
    1549951	0292992	0292992	1549951	0000000
    1551768	0000000	0000000	0000000	0000000
    1559486	0000000	0000000	0000000	0000000
    I knew the UNION was giving the duplicates, but wasn't sure if there was something that would let me merge the rows during the union...
    Last edited by joseph.miskiw; 03-23-10 at 16:46. Reason: added comments

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I believe this is what you want:

    Code:
    select o.wo_nbr,o.wkr_nbr,r.wknbr,r.wo_nbr,s.msr_nbr 
    from t2wrwrh as r 
    left outer join t2wowoh as o on ()
    left outer join t2msmsh as s on ()
    where r.master_wo_nbr = 1551785
    Andy

  9. #9
    Join Date
    Mar 2010
    Posts
    5
    well that's nice and tidy... i had to make a few changes in order for it to run, i guess it wasn't clear in my samples, but your query doesn't pull rows where there is a wo_nbr but no wkr_nbr in the t2wowoh table, i think the following is what i really want
    Code:
    select o.wo_nbr,o.wkr_nbr,r.wknbr,r.wo_nbr,s.msr_nbr 
    from t2wrwrh as r 
    full outer join t2wowoh as o on ()
    left outer join t2msmsh as s on ()
    where r.master_wo_nbr = 1551785 or o.master_wo_nbr = 1551785
    this takes a bit longer to run but i think it works...

    thanks for the help, as a self-taught programmer i'm often down a rabbit hole... it's easy to miss fundamental concepts when you teach yourself...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •