Forum Discussion

phk_Holship's avatar
phk_Holship
Copper Contributor
Mar 16, 2023
Solved

Query with a WHERE clause. How do I add more records to it?

  Hi   SQL rookie here. I have successfully created the query below, that returns records from "FinancePosting" table, for each record where it finds a match with another table called File.   Th...
  • LainRobertson's avatar
    LainRobertson
    Mar 16, 2023

    phk_Holship 

     

    Assuming the FinancePosting table represents the maximum set then I'd be using a LEFT OUTER JOIN over a CROSS JOIN since it's only potential "FileID IS NULL" on the left side we'd be wanting to include (even then, only where Period = 2400.)

     

    SELECT 
    [fp].[Period]
    , [fp].[NomID]
    , [fp].[FileID]
    , [f].[FilenumberID]
FROM 
    [database].[FinancePosting] AS [fp] LEFT OUTER JOIN
    [database].[File] AS [f] ON [fp].[FileID] = [f].[FileID]
WHERE 
    (
        [fp].[Period] BETWEEN 2200 AND 2299
        AND [f].[FileID] IS NOT NULL
    )
    OR [fp].[Period] IN (2400); -- This could be a simple "equals" comparison if you're certain there will never be other ad hoc inclusions.

     

    Cheers,

    Lain

LainRobertson's avatar
LainRobertson
Silver Contributor
Mar 16, 2023

phk_Holship 

 

Assuming the FinancePosting table represents the maximum set then I'd be using a LEFT OUTER JOIN over a CROSS JOIN since it's only potential "FileID IS NULL" on the left side we'd be wanting to include (even then, only where Period = 2400.)

 

SELECT 
    [fp].[Period]
    , [fp].[NomID]
    , [fp].[FileID]
    , [f].[FilenumberID]
FROM 
    [database].[FinancePosting] AS [fp] LEFT OUTER JOIN
    [database].[File] AS [f] ON [fp].[FileID] = [f].[FileID]
WHERE 
    (
        [fp].[Period] BETWEEN 2200 AND 2299
        AND [f].[FileID] IS NOT NULL
    )
    OR [fp].[Period] IN (2400); -- This could be a simple "equals" comparison if you're certain there will never be other ad hoc inclusions.

 

Cheers,

Lain

Resources