Forum Discussion
Query with a WHERE clause. How do I add more records to it?
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
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
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.
using the table names with commas.
On one hand I agree, on the other: That's not really a cross join, it's "old style join" syntax; avoid it
Finding code smells using SQL Prompt: old-style join syntax (ST001) | Redgate (red-gate.com)
Olaf
