ansaurus

Question

Answer 1

A:

I think you could do OUTER JOINs on the FinMaster and FinTransMaster tables, and have the where clause specify that the IDs from those tables are null.

Carl Manaster 2009-09-12 00:21:35

Answer 2

A:

You just have to do joins where is null.
Here is an example using the query you provided:

SELECT TOP 100 PERCENT
Ftm.AcctID AS [Acct Id],
Act.AccountNumber AS [Account No.],
Act.AccountTypeId,
ISNULL(Cnt.FirstName, '')+ ' ' + ISNULL(Cnt.LastName, '') AS [Full Name],
Ftm.FinTransTypeCode AS [Trans Type],
Ftm.FinTransCode AS [Trans Code],
Fm.FJNo AS [FJ No.],
Fm.ReversalFJNo AS [Reversal FJNo.],
CAST(ISNULL(Fm.FJAmt,0) AS DECIMAL(9, 2)) AS Amount,
Ftc.InterfaceDescr AS [Transaction Desc],
Fm.Comments, Fm.CreatedBy AS [Posted By],
Ftm.Created,RegistrationTypeid, FJDate

FROM FinMaster AS Fm
INNER JOIN FinTransMaster AS Ftm ON Ftm.FjNo = Fm.FJNo
INNER JOIN dbo.Account AS Act ON Ftm.AcctID = Act.AccountId
INNER JOIN dbo.Contact AS Cnt ON Act.PrimaryContactId = Cnt.ContactId
INNER JOIN FinTransCodes AS Ftc ON Ftc.FinTransCode = Ftm.FinTransCode

left outer join FinMaster as FM2 on FM2.ReversalFjNo = Fm.FJNo and FM2.ReversalFjNo is null
left outer join FinTransMaster AS Ftm2 on (Ftm2.FjNo = Ftm.FjNo) AND (Ftm2.FjTransSeqNo < Ftm.FjTransSeqNo)
   and Ftm2.FjNo is null

WHERE
(Ftm.FinTransTypeCode <> 'PYMT') AND FJDate > getdate()-5

ORDER BY Ftm.Created DESC

Avitus 2009-09-12 00:23:19

Answer 3

+2 A:

Why would you want to subject yourself to that? Good use of subqueries (especially using IN and NOT IN) makes the query more readable and they're just as fast on most database engines.

Generally, using JOINs on tables you want to select from and subqueries for everything else results in the clearest query. Here's what I would suggest.

SELECT TOP 100 PERCENT 
    Ftm.AcctID AS [Acct Id], 
    Act.AccountNumber AS [Account No.], 
    Act.AccountTypeId, 
    ISNULL(Cnt.FirstName, '')+ ' ' + ISNULL(Cnt.LastName, '') AS [Full Name], 
    Ftm.FinTransTypeCode AS [Trans Type], 
    Ftm.FinTransCode AS [Trans Code], 
    Fm.FJNo AS [FJ No.],   
    Fm.ReversalFJNo  AS [Reversal FJNo.],  
    CAST(ISNULL(Fm.FJAmt,0) AS DECIMAL(9, 2))  AS Amount, 
    Ftc.InterfaceDescr AS [Transaction Desc], 
    Fm.Comments, Fm.CreatedBy AS [Posted By],   
    Ftm.Created,RegistrationTypeid, FJDate  
FROM
    FinMaster AS Fm 
    INNER JOIN FinTransMaster AS Ftm ON Ftm.FjNo = Fm.FJNo 
    INNER JOIN dbo.Account AS Act ON Ftm.AcctID = Act.AccountId
    INNER JOIN dbo.Contact AS Cnt ON Act.PrimaryContactId = Cnt.ContactId
    INNER JOIN FinTransCodes AS Ftc ON Ftc.FinTransCode = Ftm.FinTransCode
WHERE     
    (Ftm.FinTransTypeCode <> 'PYMT') AND FJDate > getdate()-5  
    AND Fm.FJNo NOT IN (
                     SELECT ReversalFjNo
                     FROM FinMaster  
                     WHERE ReversalFjNo IS NOT NULL) 
    AND Ftm.FjNo NOT IN (
                     SELECT FjNo
                     FROM FinTransMaster AS Ftm2  
                     WHERE FjNo IS NOT NULL) 
    AND (FjTransSeqNo < Ftm.FjTransSeqNo)))  
ORDER BY Ftm.Created DESC

EvilRyry 2009-09-12 00:23:33

+1 for arguing in favor of code readability. SQL is not the easiest language to read and interpret. One caution though: it's not unusual for code readability to be balanced directly against code performance. They're not inversely proportionate per se, but care must be taken to properly document the trickier, less readable portions of code. The OP may have been gunning for an easier query to maintain or a more performant one.

David Andres 2009-09-12 01:48:12

Answer 4

+3 A:

In SQL Server, NOT IN / NOT EXISTS are better than LEFT JOIN since its optimizer can't discern an ANTI JOIN in a LEFT JOIN / IS NULL. It will return the entire resultset, and filter the NULLs out afterwards.

WITH contacts AS (
     SELECT t.contactid,
            CASE
              WHEN t.firstname IS NULL AND t.lastname IS NULL THEN
                ''
              WHEN t.firstname IS NULL THEN
                t.lastname
              WHEN t.lastname IS NULL THEN
                t.firstname
              ELSE
                t.FirstName + ' ' t.LastName
           END AS [fullname]
      FROM dbo.CONTACT t)
SELECT TOP 100 PERCENT 
       ftm.AcctID AS [Acct Id], 
       a.AccountNumber AS [Account No.], 
       a.AccountTypeId, 
       c.fullname AS [Full Name], 
       ftm.FinTransTypeCode AS [Trans Type], 
       ftm.FinTransCode AS [Trans Code], 
       t.FJNo AS [FJ No.],   
       t.ReversalFJNo  AS [Reversal FJNo.],  
       CAST(ISNULL(t.FJAmt, 0) AS DECIMAL(9, 2)) AS Amount, 
       ftc.InterfaceDescr AS [Transaction Desc], 
       t.Comments,
       t.CreatedBy AS [Posted By],   
       ftm.Created,
       RegistrationTypeid,
       FJDate  
 FROM FINMASTER t
 JOIN FINTRANSMASTER ftm ON ftm.FjNo = t.FJNo 
                        AND ftm.FinTransTypeCode <> 'PYMT'
 JOIN dbo.ACCOUNT a ON a.accountid = ftm.AcctID 
 JOIN contacts c ON c.contactid = a.PrimaryContactId
 JOIN FINTRANSCODES ftc ON ftc.FinTransCode = ftm.FinTransCode
WHERE FJDate > getdate()-5  
  AND NOT EXISTS (SELECT NULL  
                    FROM FinMaster fm  
                   WHERE fm.ReversalFjNo = t.FJNo)
  AND NOT EXISTS (SELECT NULL
                    FROM FinTransMaster AS ftm2
                   WHERE ftm2.FjNo = ftm.FjNo 
                     AND ftm2.FjTransSeqNo < ftm.FjTransSeqNo)  
ORDER BY ftm.Created DESC

OMG Ponies 2009-09-12 00:38:25

ansaurus

tags:

views:

answers:

Self Joins instead of subqueries

related questions