Geek asks and answers

Additional filter in the left column of the union

I am having problems with how to write a procedure that includes an additional filter in the left join.

I have two tables, releases and Customer_Location. Not all problems are related to the location of the client. So I'm fine with this:

SELECT
  I.Issue_Number,
  C.Customer_Location_Code

FROM Issues I
LEFT JOIN Customer_Location C
  ON C.Customer_Location_Key = I.Customer_Location_Key


Issue_Number | Customer_Location_Code
1            | Chicago
2            | NULL
3            | Chicago
4            | New York

And it works, it gives me all the problems. But I want to add an optional parameter for the client's location code, which, if left null, will return all 4 problems, but if Chicago is set to 1, only numbers 1 and 3 are returned.

I tried this

DECLARE @customer_location_key INT
SET @customer_location_key = 1

SELECT
  I.Issue_Number,
  C.Customer_Location_Code

FROM Issues I
LEFT JOIN Customer_Location C
  ON C.Customer_Location_Key = I.Customer_Location_Key
 AND C.Customer_Location_Key = @customer_location_key

But I get the following results

Issue_Number | Customer_Location_Code
1            | Chicago
2            | NULL
3            | Chicago
4            | NULL

For some reason, I seem to have a brain fart now, and I just can't figure out what SHOULD be pretty simple

+3
source share
3

where, , .

DECLARE @customer_location_key INT
SET @customer_location_key = 1

SELECT
  I.Issue_Number,
  C.Customer_Location_Code

FROM Issues I
LEFT JOIN Customer_Location C
  ON C.Customer_Location_Key = I.Customer_Location_Key
where (@customer_location_key is null or C.Customer_Location_Key = @customer_location_key)
+3

where

DECLARE @customer_location_key INT
SET @customer_location_key = 1

SELECT
  I.Issue_Number,
  C.Customer_Location_Code

FROM Issues I
LEFT JOIN Customer_Location C
  ON C.Customer_Location_Key = I.Customer_Location_Key
WHERE
 (@customer_location_key is null OR C.Customer_Location_Key = @customer_location_key)
+3

The reason your query does not work the way you expect is because conditions 2 are checked first ON, and then LEFT JOINall the rows of the table Issuethat were not matched (with NULL in the columns of the table Customer_Location_Code) are added .

DECLARE @customer_location_key INT
SET @customer_location_key = 1

SELECT
  I.Issue_Number,
  C.Customer_Location_Code

FROM Issues I
LEFT JOIN Customer_Location C
  ON C.Customer_Location_Key = I.Customer_Location_Key
WHERE ( @customer_location_key IS NULL )
   OR ( C.Customer_Location_Key = @customer_location_key )
+2
source

More articles:


All Articles