First, the two queries you offered do not need union at all, they only differ in the where. Also, the first is redundant, as you give criteria for
BOS_SD_FDo.szFsoId=' ' in the first union and then just drop it in the second.
The second query is similar, but just requires "or" logic. You can optimize both quieries like so:
sql
SELECT DISTINCT dbo.BOS_AR_Customer.szCustId, dbo.BOS_AR_Customer.szName,
dbo.BOS_SD_Fso.szfsoId as szOrdNum, dbo.BOS_SD_fsoItem.decQty, dbo.BOS_SD_fsoItem.decPrice
FROM dbo.BOS_AR_Customer
INNER JOIN dbo.BOS_SD_fso ON dbo.BOS_AR_Customer.szCustId = dbo.BOS_SD_fso.szCustId
INNER JOIN dbo.BOS_SD_fsoItem ON dbo.BOS_SD_fso.szfsoId = dbo.BOS_SD_fsoItem.szfsoId
where BOS_SD_fso.szSalesid = '001-D004'
and BOS_SD_fso.dtmOrder = '2008-03-11'
and BOS_SD_fso.bApplied = 1
select BOS_SD_FSOitem.szProductId, BOS_INV_Product.szName
from BOS_SD_FSOItem
inner join BOS_INV_Product on BOS_SD_FSOItem.szProductid = BOS_INV_PRoduct.szProductId
inner join BOS_SD_Fso on BOS_SD_FSOItem.szFsoId = BOS_SD_Fso.szFsoid
where szSalesid = '001-D004'
and ( BOS_SD_FSO.dtmorder = '2008-03-11' or BOS_SD_fdo.dtmdelivery = '2008-03-11')
Now for the merged query. First, union should only be used as a last resort! In fact, you never really have to use it at all, you can work around it if need be. Don't use union!
This is what I think you're looking for
sql
SELECT DISTINCT dbo.BOS_AR_Customer.szCustId, dbo.BOS_AR_Customer.szName,
dbo.BOS_SD_Fso.szfsoId as szOrdNum, dbo.BOS_SD_fsoItem.decQty, dbo.BOS_SD_fsoItem.decPrice,
BOS_SD_FSOitem.szProductId, BOS_INV_Product.szName
FROM dbo.BOS_AR_Customer
INNER JOIN dbo.BOS_SD_fso ON dbo.BOS_AR_Customer.szCustId = dbo.BOS_SD_fso.szCustId
INNER JOIN dbo.BOS_SD_fsoItem ON dbo.BOS_SD_fso.szfsoId = dbo.BOS_SD_fsoItem.szfsoId
inner join BOS_INV_Product on BOS_SD_FSOItem.szProductid = BOS_INV_PRoduct.szProductId
where BOS_SD_fso.szSalesid = '001-D004'
and BOS_SD_fso.bApplied = 1
and ( BOS_SD_FSO.dtmorder = '2008-03-11' or BOS_SD_fdo.dtmdelivery = '2008-03-11')
Also, this could be easier to follow with some aliases:
sql
SELECT DISTINCT cust.szCustId, cust.szName as CustName,
fso.szfsoId as szOrdNum, item.decQty, item.decPrice,
item.szProductId, prod.szName as ProductName
FROM dbo.BOS_SD_fso fso
INNER JOIN dbo.BOS_AR_Customer cust ON cust.szCustId = fso.szCustId
INNER JOIN dbo.BOS_SD_fsoItem item ON item.szfsoId = fso.szfsoId
inner join BOS_INV_Product prod on item.szProductid = prod.szProductId
where fso.bApplied = 1
and fso.szSalesid = '001-D004'
and ( fso.dtmorder = '2008-03-11' or fso.dtmdelivery = '2008-03-11')
I mixed the order up a little on the last one, I thought is scanned better, since everything is dependant on fso, not cust.
Hope this helps.
, but the result is not the way I expected. So, can you help me guys Please ???? I really do need some assistance here, I just don`t wanna quit that easy.
| 