Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

How to make SQL NOT EXISTS return zero?

SQL 2000

How can I make a NOT EXISTS return a zero?

I have tblTruckREport.ID = tblTransactionHeader.TRID

So if there is no tblTransactionHeader.TRID I need a zero value

Thanks,

Dennis
0
NDennisV
Asked:
NDennisV
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
seems like you need a left join here , can you paste the entire query
0
 
LimbeckCommented:
hi, something like this? replace tablename with the correct name

select case when tblTransactionHeader.TRID is null then 0 else tblTransactionHeader.TRID end as TRID
from tbltransaction left join tblTransactionHeader on tblTransactionHeader.TRID=tbltransaction.Trid
0
 
LimbeckCommented:
tblTruckREport.ID

select case when tblTransactionHeader.TRID is null then 0 else tblTransactionHeader.TRID end as TRID
from tblTruckREport left join tblTransactionHeader on tblTruckREport.ID=tblTransactionHeader.TRID
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
NDennisVAuthor Commented:
I'm fairly new to this. I tried LEFT JOINs but did not get zeros. Here's an actual query.
SELECT SUM(tblManifestDetail.Quantity) as 'Sum Mnfst Quantity', tblTruckReport.TruckReportNumber, tblDrivers.LastName, tblTransaction.Quantity as 'Inventory Quantity'
FROM tblManifest
INNER JOIN tblManifestDetail ON tblManifest.ID = tblManifestDetail.ManifestID
INNER JOIN tblTruckReport ON tblManifest.TRID = tblTruckReport.ID
INNER JOIN tblDrivers ON tblDrivers.Certification = tblTruckReport.CertificationID
INNER JOIN tblTransactionHeader ON tblTruckREport.ID = tblTransactionHeader.TRID
INNER JOIN tblTransaction ON tblTransactionHeader.ID = tblTransaction.TransactionHeaderID
WHERE tblManifest.ManifestDate >= '12/3/2009' AND tblManifest.ManifestDate <= '12/3/2009' AND (tblManifestDetail.MatCode LIKE '%1aga%' OR tblManifestDetail.MatCode LIKE '%3aga%') AND (tblTransaction.Material LIKE '%1aga%' OR tblTransaction.Material LIKE '%3aga%')
GROUP BY tblTruckReport.TruckReportNumber, tblDrivers.LastName, tblTransaction.Quantity
ORDER BY tblTruckReport.TruckReportNumber
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT SUM(tblManifestDetail.Quantity) as 'Sum Mnfst Quantity',
       tblTruckReport.TruckReportNumber, tblDrivers.LastName, tblTransaction.Quantity as 'Inventory Quantity'
FROM tblManifest
INNER JOIN tblManifestDetail ON tblManifest.ID = tblManifestDetail.ManifestID
INNER JOIN tblTruckReport ON tblManifest.TRID = tblTruckReport.ID
INNER JOIN tblDrivers ON tblDrivers.Certification = tblTruckReport.CertificationID
LEFT JOIN tblTransactionHeader ON tblTruckREport.ID = tblTransactionHeader.TRID
INNER JOIN tblTransaction ON tblTransactionHeader.ID = tblTransaction.TransactionHeaderID
WHERE tblManifest.ManifestDate >= '12/3/2009' AND tblManifest.ManifestDate <= '12/3/2009' AND (tblManifestDetail.MatCode LIKE '%1aga%' OR tblManifestDetail.MatCode LIKE '%3aga%') AND (tblTransaction.Material LIKE '%1aga%' OR tblTransaction.Material LIKE '%3aga%')
GROUP BY tblTruckReport.TruckReportNumber, tblDrivers.LastName, tblTransaction.Quantity
ORDER BY tblTruckReport.TruckReportNumber
0
 
NDennisVAuthor Commented:
No, that did not work.
0
 
yuchingCommented:
try this, left outer join table tblTransactionHeader  and tblTransaction

SELECT SUM(tblManifestDetail.Quantity) as 'Sum Mnfst Quantity', tblTruckReport.TruckReportNumber, tblDrivers.LastName, tblTransaction.Quantity as 'Inventory Quantity'
FROM tblManifest
INNER JOIN tblManifestDetail ON tblManifest.ID = tblManifestDetail.ManifestID
INNER JOIN tblTruckReport ON tblManifest.TRID = tblTruckReport.ID
INNER JOIN tblDrivers ON tblDrivers.Certification = tblTruckReport.CertificationID
Left Outer JOIN tblTransactionHeader ON tblTruckREport.ID = tblTransactionHeader.TRID
Left Outer JOIN tblTransaction ON tblTransactionHeader.ID = tblTransaction.TransactionHeaderID
WHERE tblManifest.ManifestDate >= '12/3/2009' AND tblManifest.ManifestDate <= '12/3/2009' AND (tblManifestDetail.MatCode LIKE '%1aga%' OR tblManifestDetail.MatCode LIKE '%3aga%') AND (tblTransaction.Material LIKE '%1aga%' OR tblTransaction.Material LIKE '%3aga%')
GROUP BY tblTruckReport.TruckReportNumber, tblDrivers.LastName, tblTransaction.Quantity
ORDER BY tblTruckReport.TruckReportNumber
0
 
NDennisVAuthor Commented:
It works when I did it this way. I'm going to award you the points. Thank you.
SELECT SUM(tblManifestDetail.Quantity) as 'Sum Mnfst Quantity', tblTruckReport.TruckReportNumber, tblDrivers.LastName, tblTransaction.Quantity as 'Inventory Quantity'
FROM tblManifest
INNER JOIN tblManifestDetail ON tblManifest.ID = tblManifestDetail.ManifestID
LEFT JOIN tblTruckReport ON tblManifest.TRID = tblTruckReport.ID
INNER JOIN tblDrivers ON tblDrivers.Certification = tblTruckReport.CertificationID
Left Outer JOIN tblTransactionHeader ON tblTruckREport.ID = tblTransactionHeader.TRID
Left Outer JOIN tblTransaction ON tblTransactionHeader.ID = tblTransaction.TransactionHeaderID
WHERE tblManifest.ManifestDate >= '12/3/2009' AND tblManifest.ManifestDate <= '12/3/2009' AND (tblManifestDetail.MatCode LIKE '%1aga%' OR tblManifestDetail.MatCode LIKE '%3aga%') AND (tblTransaction.Material LIKE '%1aga%' OR tblTransaction.Material LIKE '%3aga%')
GROUP BY tblTruckReport.TruckReportNumber, tblDrivers.LastName, tblTransaction.Quantity
ORDER BY tblTruckReport.TruckReportNumber
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now