I tried to join some Products based on if they're in a table or not. I'm using MSSQL and I'm trying to do this to see if the category has some products.
simplified Query:
SELECT c.CategoryID, c.Name, p.ProductID
FROM Category AS c
JOIN Product AS p ON p.ProductID IN (
SELECT PrductID FROM exampleTable
)
ProductTable:
| ProductID | CategoryID |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 4 |
The output I receive:
| CategoryID | Name | ProductID |
|---|---|---|
| 1 | Cat1 | 1 |
| 1 | Cat1 | 2 |
| 2 | Cat2 | 3 |
| 4 | Cat4 | 4 |
The expected output:
| CategoryID | Name | ProductID |
|---|---|---|
| 1 | Cat1 | 1 |
| 2 | Cat2 | 3 |
| 4 | Cat4 | 4 |
I'm trying to only join a product if it's in the select statement and not join all products which have the same category id.
In pseudo code I'm trying to achive this:
JOIN Product AS p IF p.ProductID IN (Subquery)
Is this somehow possible?
CodePudding user response：
This is achievable using inner join.
SELECT c.CategoryID, c.Name, p.ProductID
FROM Category AS c
INNER JOIN Product AS p ON p.CategoryID = c.CategoryID
INNER JOIN exampleTable t on t.ProductID = p.ProductID
CodePudding user response：
Ed banga's answer is IMHO more elegant and perfoment but to be closer to what you proposed in your question, you can simply use a where clause.
SELECT c.CategoryID, c.Name, p.ProductID
FROM Category AS c
JOIN Product AS p ON p.CategoryID = c.CategoryID
WHERE p.ProductID IN (
SELECT PrductID FROM exampleTable
)