Select rows seperately from two joined tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select rows seperately from two joined tables

Author

Topic

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2014-11-05 : 08:28:34

I have a query which returns all parts and labour lines for a particular work order. It returns all parts lines seperately, but the labour lines are repeated for each row. What I want to accomplish for a given work order, is a list of all the parts lines, followed underneath by a list of all labour lines.

This is the code from the report:

select 
	h.worknumber,

--- Select parts lines and charges

	wp.description as [charges desc],
	case
	when wp.charge_to_cust = 1 then wp.sale_price
	else 0
	end [UnitSalePrice],
	isnull(wp.qty,0) ItemQty,
	case
	when wp.charge_to_cust = 1 then wp.sale_price*wp.qty
	else 0
	end [SalePrice],
	case
		when isnull(h.actual_parts_sale,0)<>0 and wp.charge_to_cust <> 0  then isnull(h.actual_parts_sale,0)
		when wp.charge_to_cust = 0 then 0		
		else isnull(h.est_parts_sale,0)
	end est_parts_sale,

-- Select labour lines and charges

	 wl.description as [labour desc],
	case
	when wl.charge_to_cust = 1 then wl.charge_rate
	else 0
	end [LabUnitPrice],
	isnull(wl.hours,0) LabItemQty,
	case
	when wl.charge_to_cust = 1 then wl.charge_rate*wl.hours
	else 0
	end [LabSalePrice],
		
	case
		when isnull(h.actual_labour_sale,0)<>0 and wl.charge_to_cust <> 0  then isnull(h.actual_labour_sale,0)
		when wl.charge_to_cust = 0 then 0		
		else isnull(h.est_labour_sale,0)
	end est_labour_sale

	From
	worksorderhdr h

left outer join
	worksorderparts wp on h.worknumber = wp.worknumber
left join
	 worksorderlabour wl on wl.worknumber = h.worknumber

Current Output:

worknumber           charges desc                                                                                                                                                                                                                                                    UnitSalePrice         ItemQty     SalePrice             est_parts_sale        labour desc                                                                                                                                                                                                                                                     LabUnitPrice          LabItemQty             LabSalePrice           est_labour_sale
-------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------- ----------- --------------------- --------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------- ---------------------- ---------------------- ---------------------
WA-DN-002953         OUR ENGINEER SENT TO SITE TO REPLACE THE FOLLOWING PARTS-                                                                                                                                                                                                       0.00                  1           0.00                  110.85                LABOUR                                                                                                                                                                                                                                                            35.00                 0.5                    17.5                   17.50
WA-DN-002953         SED FUEL FILTER AGRI / TIER 4                                                                                                                                                                                                                                   25.95                 1           25.95                 110.85                LABOUR                                                                                                                                                                                                                                                            35.00                 0.5                    17.5                   17.50
WA-DN-002953         FILTER A120                                                                                                                                                                                                                                                     52.50                 1           52.50                 110.85                LABOUR                                                                                                                                                                                                                                                            35.00                 0.5                    17.5                   17.50
WA-DN-002953         FILTER A124                                                                                                                                                                                                                                                     32.40                 1           32.40                 110.85                LABOUR                                                                                                                                                                                                                                                            35.00                 0.5                    17.5                   17.50

For this example what I'd like to see is 5 lines here - the labour description and charge under charges description, unit price, qty and est_parts_sale etc, and of course, there could be more than 1 labour line.

Help with modifying this code would be very much appreciated.

Many thanks
Martyn

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-11-05 : 11:48:23

[code]select
h.worknumber,
wp.whatever
From
worksorderhdr h
left outer join
worksorderparts wp on h.worknumber = wp.worknumber

union all

select
h.worknumber,
wl.whatever
From
worksorderhdr h
left join
worksorderlabour wl on wl.worknumber = h.worknumber[/code]???

No amount of belief makes something a fact. -James Randi

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2014-11-05 : 12:37:59

That's great, thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources