April 26, 2008 at 11:52 am
Hello.
I have a select that returns some SubCategories.
SELECT SubCategoryID from SubCategories SCAT
INNER JOIN Categories CAT on CAT.CategoryId = SCAT.ParenCategoryId
WHERE SCAT.ParentCategoryId = X
Now i will to retrieve all rows from a Table called Items Where Items.SubCategoryId will be any of the previous SubCategoryId's returned by the above SELECT query.
What code i need to write to
I think .. Select * from Items AS IT WHERE IT.Subcategory = ???
I don't know, anyone can help me
Thanks
April 26, 2008 at 12:53 pm
Select *from items where SubCategoryId IN (SELECT SubCategoryID from SubCategories SCAT
INNER JOIN Categories CAT on CAT.CategoryId = SCAT.ParenCategoryId
WHERE SCAT.ParentCategoryId = X)
maybe not the best but works 😛
goodyes (4/26/2008)
Hello.I have a select that returns some SubCategories.
SELECT SubCategoryID from SubCategories SCAT
INNER JOIN Categories CAT on CAT.CategoryId = SCAT.ParenCategoryId
WHERE SCAT.ParentCategoryId = X
Now i will to retrieve all rows from a Table called Items Where Items.SubCategoryId will be any of the previous SubCategoryId's returned by the above SELECT query.
What code i need to write to
I think .. Select * from Items AS IT WHERE IT.Subcategory = ???
I don't know, anyone can help me
Thanks
April 26, 2008 at 1:03 pm
I will try, but its a very difficult Beacuse the Select Statement includes CTE
April 26, 2008 at 1:33 pm
Your example does not clearly show a CTE, but if I presume your first select statement is the CTE then the way you would select items would be like this:
;
WITH CID
AS (SELECT SubCategoryID
FROM SubCategories SCAT
INNER JOIN Categories CAT
ON CAT.CategoryId = SCAT.ParenCategoryId
WHERE SCAT.ParentCategoryId = X)
SELECT *
FROM Items AS IT
JOIN CID
ON IT.Subcategory = CID.SubCategoryID
I define the Common table expression called CID, then simply do a join to Items with the SubCategoryID's that are returned from the CTE.
HTH
Jo
April 26, 2008 at 3:24 pm
THANKS I Resolve IT !!, ..
The CTE are on a SP (example SP_CTEOfClassifications)
I WRITE THE FOLLOWING CODE
FIRST I CREATE A TABLE
CREATE table TEMP_Classifications(all columns that retrieve the SP_CTEOfClassifications)
SECOND: USING INSERT EXEC; the code inserts all rows from SP_CTEOfClassifications to the TEMP_Classifications
INSERT TEMP_Classifications EXEC dbo.GetClassifications @Params = values
AT THIS POINT the code fill this temp table with the required rows to witch apply the needed WHERE clause
THIRD: I APPLY YOUR SUGGESTION
SELECT SomeCols FROM SomeTable
WHERE ColumnIWantToCheckForMultipleValues IN (SELECT ColumnIWantToCheckForMultipleValues from TEMP_Classifications)
THANKS
I write the above code as an example for other guys.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply