Need help to simplify my Search Query
-
SELECT A1.JoinID from Join A1, Product A2, Location A3 WHERE
A1.ProductID = A2.ProductID AND
A1.LocationID = A3.LocationID AND
A2.ProductName = "Product1" AND
A3.LocationName = "Location1"
UNION
SELECT A1.JoinID from Join A1, Product A2, Location A3 WHERE
A1.ProductID = A2.ProductID AND
A1.LocationID = A3.LocationID AND
A2.ProductName = "Product2" AND
A3.LocationName = "Location1"
UNION
SELECT A1.JoinID from Join A1, Product A2, Location A3 WHERE
A1.ProductID = A2.ProductID AND
A1.LocationID = A3.LocationID AND
A2.ProductName = "Product1" AND
A3.LocationName = "Location2"
UNION
SELECT A1.JoinID from Join A1, Product A2, Location A3 WHERE
A1.ProductID = A2.ProductID AND
A1.LocationID = A3.LocationID AND
A2.ProductName = "Product2" AND
A3.LocationName = "Location2"
I have this search option whereby users can select multiple products and multiple locations. The SQL query above is only for 2 products vs 2 locations (Product1, Location1), (Product2, Location1), (Product1, Location2) AND (Product2, Location2).
What if I have 10 products and 10 locations? What is the best way to simply my query?
-
It appears to me that you're wanting all combinations, and if so, wouldn't this (untested) work for you?
SELECT
j.JoinID
FROM
Join AS j
INNER JOIN Product AS p ON
j.ProductID = p.ProductID
INNER JOIN Location AS l ON
j.LocationID = l.LocationID
WHERE
p.ProductName IN ('Product1', 'Product2') -- etc.
AND l.LocationName IN ('Location1', 'Location2') -- etc.
-
Are you sending this from an application as a SQL query string? If so, you can modify the SQL code to add as many products and locations as needed using David's code.
Otherwise, if you're using a stored procedure or wish to otherwise parameter the query, you'll probably want to pass the product IDs and location IDs as delimited strings. Then you can either
a) Use dynamic SQL to build a SQL string in the stored proc - not nice but often done for searches such as yours
b) Split the strings apart and populate some temporary tables, then join against them.
Not sure how much SQL knowledge you have but to implement the first option should be easy, option (a) is ok if done well, option (b) needs you to look up topics on this site such as "passing an array to a stored procedure"
-
Okay.
I am grabbing the IDs from my C++ application.
Anyways, thanks guys. I will try the solutions and see how it goes.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply