Forum Discussion

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

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

Silver Contributor

Mar 16, 2023

phk_Holship

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

phk_Holship

Copper Contributor

Mar 17, 2023

LainRobertson

Awesome thanks both you guys!

The first example did not work for some reason, it just starts loading data but never loads anything.

Second example worked like a charm. So seems to be some issue with using cross join.