CODEWITHSUNDEEP
sqlselectforeign-keys
Awatt
Awatt

Reputation: 37

select from three table

i have three table Product, MapCatProduct, Category.

Table definitions:

Table 1: Product

Column 1: int ProductID (PK)
Column 2: nvarchar(255) Name

Table 2: MapCatProduct

Column 1: int MapCatProductID (PK)
Column 2: int ProductID(FK)
Column 3: int CategoryID(FK)

Mapping table: Category

Column 1: int CategoryID (PK)
Column 2: nvarchar(255) Name

I want select Product table when ProductID is input Parameter and categoryID this parameters Equals all results.

for eaxample: (InputParameters) ProductID = 14

results:

ProductID = 14 CategoryID = 2

ProductID = 34 CategoryID = 2

ProductID = 63 CategoryID = 2

notice: it is possible that one productID have 'n' CategoryID

My Code is:

SELECT p.Id,p.Name FROM Product p 
WHERE p.Id = @Id --input parameter
UNION
(SELECT TOP 5 p.Id,p.Name FROM Product p
INNER JOIN MapCatProduct mcp
ON p.Id = mcp.ProductId
INNER JOIN Category c
ON c.Id = mcp.CategoryId)

Upvotes: 1

Views: 107

Answers (2)

LukStorms
LukStorms

Reputation: 29677

You should first change the types of those foreign keys to the same type as used in the foreign table. In this case INT.
No need to use a large nvarchar for a column that only keeps integers.

After that you can just join the map table to the other 2 tables.
For example: 

select 
 prod.ProductID, cat.CategoryID, 
 prod.Name as ProductName, cat.Name as CategoryName
from MapCatProduct map
join Product prod on prod.ProductID = map.ProductID
join Category cat on cat.CategoryID = map.CategoryID
where map.CategoryID in (
   select distinct CategoryID 
   from MapCatProduct
   where ProductID = @ProductId 
)

But of course, if you only need the id's, then selecting only from the map table is sufficient.

select ProductID, CategoryID
from MapCatProduct
where CategoryID in (
   select distinct CategoryID 
   from MapCatProduct
   where ProductID = @ProductId 
)

Upvotes: 1

Yaniv Peretz
Yaniv Peretz

Reputation: 1168

You want all ProductID in the same category on the input ProductID? 

SELECT ProductID, CategoryID
FROM MapCatProductID
WHERE CategoryID = 
(SELECT DISTINCT CategoryID
FROM MapCatProductID
WHERE ProductID = <input Param>)

Upvotes: 0

Related Questions