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.

 

The query works.

 

What I want to do is add some more records to the result, that don't have any matches with File. I want to add records where FinancePosting.NomID is equal to 2400. 

 

I can't figure out how to do that. I have tried to insert an "OR" clause, like "...OR NomID=2400" but then the query does not load. Have also tried to use "UNION" but it returns an error.

SELECT 
FinancePosting.Period, 
FinancePosting.NomID, 
FinancePosting.FileID, 
[File].FilenumberID 
FROM 
database.FinancePosting, database.[File] 
WHERE 
FinancePosting.Period>=2200 AND FinancePosting.Period<=2299 
AND FinancePosting.FileID = [File].FileID

 

  • 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

4 Replies

  • 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

  • Ronen_Ariely's avatar
    Ronen_Ariely
    MVP
    Mar 16, 2023

    Hi phk_Holship 

    (1) I recommend not using the select-from-multiple-tables format using the table names with commas. This is an implicit CROSS JOIN action between the table and I highly recommend to use CROSS JOIN explicitly

    Instead of `FROM database.FinancePosting, database.[File]` it's prefered in my opinion to use `FROM database.FinancePosting CROSS JOIN database.[File]`

     

    > The query works.

    (2) What do you mean the query works if it does not return the requested information?!?

    It sound like the query run without error but does not work for you.

     

    > I want to add records where FinancePosting.NomID is equal to 2400. 

    (3) If you want to add more recording that does not fit the current filter then you need to use `OR` condition. This way you get all the rows which fit the first filter and all the rows which fit the second filter.

    Note! It could help if you provided a table and some data to work with (queries to create the tables and insert some rows for the sample)!

     

    In general (without testing since you did not provided the information I mentioned) it should be something like:

     

    SELECT 
FinancePosting.Period, 
FinancePosting.NomID, 
FinancePosting.FileID, 
[File].FilenumberID 
FROM 
database.FinancePosting, database.[File] 
WHERE
(
FinancePosting.Period BETWEEN 2200 AND 2299 
AND FinancePosting.FileID = [File].FileID
)
OR FinancePosting.NomID = 2400

     

     

    Note: if this does not solve your needs then please provide

    1) Queries to CREATE your table(s) including indexes
    2) Queries to INSERT sample data.
    3) The desired result given the sample, as text or image of excel for example.
    4) A short description of the business rules, and how you got 1-2 of the results
    5) Which version of SQL Server you are using (this will help to fit the query to your version).

     

    Regards,

Resources