Geek asks and answers

Sophisticated SQL query - finding elements matching several different foreign keys

So, imagine that you have a table Products (ID int, Name nvarchar(200))and two other tables, ProductsCategories (ProductID int, CategoryID int)and InvoiceProducts (InvoiceID int, ProductID int).

I need to write a query to create a set of products that correspond to a given set of account identifiers and category identifiers, so that the list of products matches all specified categories and all specified accounts, without returning to dynamic SQL. Imagine that I need to find a list of products that are in both categories 1 and 2, as well as in accounts 3 and 4.

At the beginning, I wrote a stored procedure that takes category identifiers and account identifiers as strings, and analyzes them in tables:

 CREATE PROCEDURE dbo.SearchProducts (@categories varchar(max), @invoices varchar(max))
 AS BEGIN
      with catids as (select cast([value] as int) from dbo.split(@categories, ' ')),
           invoiceids as (select cast([value] as int) from dbo.split(@invoices, ' '))
           select * from products --- insert awesomeness here
 END

, , . , , - , , .

: , , . ? ?

with catids as (select distinct cast([value] as int) [value] from dbo.split(@categories, ' ')),
  invoiceids as (select distinct cast([value] as int) [value] from dbo.split(@invoices, ' '))

  select pc.ProductID from ProductsCategories pc (nolock)
    inner join catids c on c.value = pc.CategoryID 
    group by pc.ProductID 
    having COUNT(*) = (select COUNT(*) from catids)  
  intersect
  select ip.ProductID from InvoiceProducts ip (nolock)
    inner join invoiceids i on i.value = ip.InvoiceID 
    group by ip.ProductID 
    having COUNT(*) = (select COUNT(*) from invoiceids)
+2
5

(ProductID, CategoryID) (ProductID, InvoiceID):

SELECT  ProductID
FROM    (
        SELECT  ProductID
        FROM    ProductInvoice
        WHERE   InvoiceID IN (1, 2)
        UNION ALL
        SELECT  ProductID
        FROM    ProductCategory pc
        WHERE   CategoryID IN (3, 4)
        ) q
GROUP BY
        ProductID
HAVING  COUNT(*) = 4

, CSV:

WITH    catids(value) AS
        (
        SELECT  DISTINCT CAST([value] AS INT)
        FROM    dbo.split(@categories, ' '))
        ), 
        (
        SELECT  DISTINCT CAST([value] AS INT)
        FROM    dbo.split(@invoices, ' '))
        )
SELECT  ProductID
FROM    (
        SELECT  ProductID
        FROM    ProductInvoice
        WHERE   InvoiceID IN
                (
                SELECT  value
                FROM    invoiceids
                )
        UNION ALL
        SELECT  ProductID
        FROM    ProductCategory pc
        WHERE   CategoryID IN
                (
                SELECT  value
                FROM    catids
                )
        ) q
GROUP BY
        ProductID
HAVING  COUNT(*) = 
        (
        SELECT  COUNT(*)
        FROM    catids
        ) + 
        (
        SELECT  COUNT(*)
        FROM    invoiceids
        )

, SQL Server 2008 .

+1

- , . Temp .

select p.*
from
(
    select pc.*
    from catids c
    inner join ProductsCategories pc
        on pc.CategoryID = c.value
) catMatch
inner join
(
    select pin.*
    from invoiceids i
    inner join ProductsInvoices pin
        on pin.InvoiceID = i.value
) invMatch
    on invMatch.ProductID = catMatch.ProductID
inner join Products p
    on p.ID = invMatch.ProductID
0

CTE?

, -SQL, :

;WITH cte AS(
Base case: Select productid, criteria from products left join criteria where row_number = 1 if it matches criteria from both row 1s or one is null.
UNION ALL
Recursive case: Select n+1 criteria row from products left join criteria where row_number = cte.row_number + 1 AND matches criteria from both row_number + 1 or one or the other (but not both) is null
)
SELECT *
WHERE criteria = maximum id from criteria table.

AND .

? CTE, .

cte, , .

0

ProductCategories should have a clustered index (CategoryId, ProductId) and InvoiceProducts should have one in (InvoiceId, ProductId) optimally. This allows you to find product identifiers based on categories and InvoiceId, using data only in clustered indexes.

You can use the function to return the int table given by the row. Google "CsvToInt" and click on the first link from SqlTeam to see the code.

Then you could:

SELECT *
FROM Products
WHERE ID IN (SELECT DISTINCT ProductId 
        FROM ProductCategories
        WHERE CategoryId in dbo.CsvToInt(@categories)
    ) AND ID IN (SELECT DISTINCT ProductId 
        FROM InvoiceProducts
        WHERE InvoiceId in dbo.CsvToInt(@invoices)
    )
0
source

Pass them as an XML parameter, save them in the temp table and join.

-1
source

More articles:


All Articles