Multiple Column Joins
-
I run a site where people can sell specific products. I have a products table that contains all the different products that can be bought and sold. Users can then upload a spreadsheet to bulk list items for sale. In the spreadsheet, users can specify any or all of the following columns to help my system determine which product they wish to sell: ProductName, Category, SubCategory, Type, and ProductNumber. A stored procedure then takes the items in their upload (stored in a temporary table) and matches those five columns against the Products DataBase to determine which product (if any) is a match.
The problem I'm running into is: The user can specify any of the five columns, but may leave some blank. I am doing a join to the products table on all five columns, but I don't want to join on a column if the user entered value for that column is NULL. After disregarding all the NULL value columns, I want to join on the remaining columns and see if there is a single record returned from the Products table (the product the user wishes to list).
Here's the SQL that I'm using, but it doesn't seem to be working correctly. My guess is that I can't use ISNULL and replace column from the second table with the column from the first. I'm sure there is a better way to do this, but I don't know how yet (still learning):
SET @PCount = (SELECT COUNT(*) FROM Products AS pr INNER JOIN ErrorListings AS e ON
pr.Category=ISNULL(e.Category, pr.Category) AND
pr.SubCategory=ISNULL(e.SubCategory, pr.SubCategory) AND
pr.Typea=ISNULL(e.Typea, pr.Typea) AND
pr.ProductName=ISNULL(e.ProductName, pr.ProductName) AND
pr.ProductSortNumber=ISNULL(e.ProductSortNumber, pr.ProductSortNumber) WHERE ErrorID=@ErrorID)
Can anyone help me get this right?
-
Try change all ISNULL to COALESCE instead. That should do waht you want..
/Kenneth
-
Coalesce will have the same effect as IsNull in this case. Coalesce allows for more than one Null comlumn to be checked and go thru a list of checks, IsNull is limited to 2.
However I am not sure I understand you issue correctly and work better wiht a visual. Can you post a sample of the data like you are dealing with and give us an example of the output you expect?
-
Indeed it does.
In that case, I wonder what it is that's 'not working'... The query is correct written as is it seems.
If it still doesn't work as expected, all I can think of is that the client (errorlistings table) doesn't send proper null values. (perhaps it's spaces instead... or the string 'null' ...
If the latter, then maybe NULLIF would fix it.
pr.Category=ISNULL(NULLIF(e.Category,' '), pr.Category)
..other than that, I'm lost...
/Kenneth
-
Thanks for your help! I'm not sure why the query didn't work, however I was able to work around the problem by using a table variable to hold possible matches, and then if statements to delete out ones that didn't work.
-
Hi, I know you have a work arround but I thought you might like to see this anyway. I'm not clear on what you wand but I think this might be it. This 'trick' also works nicely in a where clause which uses a series of optional parameters
SET @PCount = (SELECT COUNT(*) FROM Products AS pr INNER JOIN ErrorListings AS e ON
(pr.Category= e.Category OR e.Category IS NULL) AND
(pr.SubCategory=e.SubCategory OR e.SubCategory IS NULL) AND
(pr.Typea=e.Typea OR e.Typea IS NULL) AND
(pr.ProductName=e.ProductName OR e.ProductName IS NULL) AND
(pr.ProductSortNumber=e.ProductSortNumber OR e.ProductSortNumber IS NULL)
WHERE ErrorID=@ErrorID)
Barring typing errors that should do it.
The difference is subtle. In your solution the query is actually saying that when for example e.Category is null then pr.Category must also be null for the join to work rather then wah you want which is when e.Category is null then you don't care what pr.Category is.
for use in the where clause using SP parameters swap it to this
(pr.Category= @Parm1 OR Parm1 IS NULL) ... etc
Cheers.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply