Geek Answers Handbook

From many to many requests

I am trying to write code to pull a list of product items from a SQL Server database to display the results on a web page.

The requirement of the project is that the list of categories is displayed on the right side of the page as a list of flags (all categories selected by default), and the user can disable the categories and re-query the database to view products only in those categories that they want.

Here, where he starts a little hairy.

Each product can be rated in several categories using the product category table as shown below.

Product table
[product_id] (PK), [product_name], [product_price], [isEnabled], etc ...

Category table
[CategoryID] (PK), [CategoryName]

ProductCagetory table

[id] (PK), [CategoryID] (FK), [ProductID] (FK)

, , , .

proc varchar, .. (3,5,8,12)

SQL varchar temp .

?

+5
5

,

SELECT product.*
FROM   product
JOIN   ProductCategory ON ProductCategory.ProductID = Product.product_id
JOIN   #my_temp ON #my_temp.category_id = ProductCategory.CategoryID

, , . , DISTINCT . product.*, , , , , ,

-2

. SQL Server. , , - . , XML Table-Valued-Parameter ( SQL Server 2008) @variable #temp. , .

, . : , , - . , , , , - , , .

, , , , . - . :

select ...
from Products p
where p.IsEnabled = 1
and exists (
  select 1  
  from ProductCategories pc
  join #selectedCategories sc on sc.CategoryID = pc.CategoryID
  where pc.ProductID = p.ProductID);

, , (ProductID, CategoryID) (CategoryID, ProductID) ( - , - NC). btw. , , . , :

with distinctProducts as (
select distinct pc.ProductID
from ProductCategories pc
join #selectedCategories sc on pc.CategoryID = sc.CategoryID)
select p.*
from Products p
join distinctProducts dc on p.ProductID = dc.ProductID;

, . , ( 99% ), .

+2

, , ( ):

select * from product p1 join (
  select p.product_id from product p 
  join ProductCategory pc on pc.product_id = p.product_id
  where pc.category_id in (3,5,8,12)
  group by p.product_id having count(p.product_id) = 4
) p2 on p1.product_id = p2.product_id

4 - .

, ( , ):

select * from product p1 join (
  select product_id from product p1 
  where not exists (
    select * from product p2 
    join ProductCategory pc on pc.product_id = p2.product_id
    where p1.product_id = p2.product_id
    and pc.category_id not in (3,5,8,12)
  )
  group by product_id having count(product_id) = 4
) p2 on p1.product_id = p2.product_id

: , , .

:

select * from product p1 where exists (
  select * from product p2 
  join ProductCategory pc on pc.product_id = p2.product_id
  where 
    p1.product_id = p2.product_id and
    pc.category_id in (3,5,8,12)
)
+1

. Yo -.

select distinct p.* 
from product p, productcategory pc
where p.product_id = pc.productid
and pc.categoryid in ( place your comma delimited category ids here)

, , JNK, . , i.e, , , , .

-1

- , product_id , - ( , ):

SELECT distinct(p.product_id)
FROM product_table p
JOIN productcategory_table pc
ON p.product_id=pc.product_id
WHERE pc.category_id in (3,5,8,12);

, product_id, productcategory_table:

SELECT distinct(product_id)
FROM productcategory_table
WHERE category_id in (3,5,8,12);
-1

More articles:


All Articles