[DarenR]

so i have the following query:

SELECT basepoundsperhour, 
       txtproductname,
	   tplantproducts.ProductID,
	   isnull(poundsperhour,0) as  poundsperhour,
	 tHourlyTransactions.HourlyTransactionsID
from tproduct
inner join tplantproducts
ON tproduct.productid = tplantproducts.productid  
left outer join tHourlyTransactions
on tproduct.productid = tHourlyTransactions.productid
left outer join tShiftDetails 
on tShiftDetails.ShiftIDetailD = tHourlyTransactions.ShiftID 

i need it to always return

basepoundsperhour,
txtproductname,
tplantproducts.ProductID

and then see if those items exist in the tHourlyTransactions table if not just return 0 for isnull(poundsperhour,0) as poundsperhour


so for example: as must always return

sand 1000 1
salt 50000 2

example of what it should return

sand 1000 1  500 34
salt 50000 2 0    

[modi123_1]

I think you want just plain left joins.

[DarenR]

i modi "fied" as follows

SELECT basepoundsperhour, 
       txtproductname,
	   tplantproducts.ProductID,
	   isnull(poundsperhour,0) as  poundsperhour,
	   tHourlyTransactions.HourlyTransactionsID 
from tproduct
inner join tplantproducts
ON tproduct.productid = tplantproducts.productid  
left  join tHourlyTransactions
on tproduct.productid = tHourlyTransactions.productid
inner join tShiftDetails 
on tShiftDetails.ShiftIDetailD = tHourlyTransactions.ShiftID 


WHERE  tplantproducts.plantid = @PlantID 
and tHourlyTransactions.ShiftID = @ShiftID
       AND tplantproducts.asactive = 1 

this is what it returns:

basepoundsperhour	txtproductname	ProductID	poundsperhour	HourlyTransactionsID
20300.00	BUFFINGS	2	1000.00	2

however it doesnt return the items as listed above. it only returns items if they exist in the tHourlyTransactions table

what it should return is

basepoundsperhour	txtproductname	ProductID	poundsperhour	HourlyTransactionsID
20300.00	           BUFFINGS	   2	           1000.00	            2
19000                      Nuggets          1                  0                    

This post has been edited by DarenR: 05 June 2019 - 11:25 AM

[modi123_1]

Trying to recreate the table info, and taking a stab at the outcome.. the left join throws in nulls for anything it can't match which you convert to 0s..

create table #tproduct(productid int, basepoundsperhour varchar(15), txtproductname varchar(15))
create table #tplantproducts(productid int, poundsperhour int)
--create table #tHourlyTransactions(productid int, HourlyTransactionsID int)

insert into #tproduct(productid, basepoundsperhour, txtproductname) values (1, 1000, 'sand')
insert into #tproduct(productid, basepoundsperhour, txtproductname) values (2, 5000, 'salt')
insert into #tproduct(productid, basepoundsperhour, txtproductname) values (3, 7000, 'sea monkeys')
insert into #tplantproducts(productid, poundsperhour) values(1, 30)


SELECT basepoundsperhour
,txtproductname
,#tproduct.ProductID
,isnull(poundsperhour,0) as  poundsperhour
from #tproduct
left join #tplantproducts
ON #tproduct.productid = #tplantproducts.productid 

--drop table #tHourlyTransactions
drop table #tplantproducts
drop table #tproduct

basepoundsperhour txtproductname  ProductID   poundsperhour
----------------- --------------- ----------- -------------
1000              sand            1           30
5000              salt            2           0
7000              sea monkeys     3           0

[DarenR]

this works until i put in a where

SELECT basepoundsperhour, 
       txtproductname,
	   tplantproducts.ProductID,
	   isnull(poundsperhour,0) as  poundsperhour,
	   isnull(tHourlyTransactions.HourlyTransactionsID, 0) as HourlyTransactionsID
from tproduct
left join tplantproducts
ON tproduct.productid = tplantproducts.productid  
left  join tHourlyTransactions
on tproduct.productid = tHourlyTransactions.productid
left join tShiftDetails 
on tShiftDetails.ShiftIDetailD = tHourlyTransactions.ShiftID 
 

WHERE  tplantproducts.plantid = @PlantID 
and tHourlyTransactions.ShiftID = @ShiftID
and tHourlyTransactions.TransStartTime = @TransTime
and tHourlyTransactions.Trans_Date = @Trans_Date
       AND tplantproducts.asactive = 1 

as soon as i put in the where it only brings back items in the tHourlyTransactions table even when it is an outer join


this is the transactions table:

HourlyTransactionsID	ShiftID	ProductID	Trans_Date	TransStartTime	PoundsPerHour	asActive
1	1	2	2019-06-05	7:30 AM	0.00	0
2	2	2	2019-06-05	7:30 AM	1000.00	0

This post has been edited by DarenR: 05 June 2019 - 11:54 AM

[modi123_1]

Not sure.. that query and the data don't match up on columns..

Do you have the basic table defs for the columns you need, and what you are trying to get out of them?

[DarenR]

this did the trick

@TransTime varchar(25) = '5:30 PM',
@ProductID int = 1,
@asActive int = 0 ,
@PlantID int = 1,
@ShiftID int = 11,
@Trans_Date date = '2019-06-06'

SELECT tproduct.txtproductname,tplantproducts.basepoundsperhour, tplantproducts.ProductID, 
isnull(tHourlyTransactions.poundsperhour,0) as  poundsperhour,
tHourlyTransactions.ShiftID
FROM tproduct 
INNER JOIN tplantproducts
ON  tproduct.productid = tplantproducts.productid 
AND  tplantproducts.plantid = @PlantID 
LEFT OUTER JOIN tHourlyTransactions
ON tHourlyTransactions.ProductID = tplantproducts.ProductID
AND tHourlyTransactions.TransStartTime = @TransTime
AND tHourlyTransactions.Trans_Date = @Trans_Date
and tHourlyTransactions.ShiftID = @ShiftID

txtproductname	basepoundsperhour	ProductID	poundsperhour	ShiftID
NUGGETS	29000.00	1	0.00	NULL
BUFFINGS	20300.00	2	0.00	NULL