DISTINCT Column but multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DISTINCT Column but multiple tables

Author

Topic

jamesnoony
Starting Member

2 Posts

Posted - 2006-03-16 : 15:15:25

Hi
I have a recordset the pulls the products from a stored procedure.

I need a drop down list that will pull DISTINCT BrandName's from those results. I would then use that list to filter the results. How should I approach this? Do I need to create another recordset or can I use the rsProducts?

Thanks, James

<%
Dim rsProducts__SubCatID
rsProducts__SubCatID = "1"
If (Request("SubCatID") <> "") Then
rsProducts__SubCatID = Request("SubCatID")
End If
%>
<%
Dim rsProducts__BrandName
rsProducts__BrandName = "%"
If (Request("BrandName") <> "") Then
rsProducts__BrandName = Request("BrandName")
End If
%>
<%
Dim rsProducts
Dim rsProducts_numRows

Set rsProducts = Server.CreateObject("ADODB.Recordset")
rsProducts.ActiveConnection = MM_connChameleon_STRING
rsProducts.Source = "{call spProducts(" + Replace(rsProducts__SubCatID, "'", "''") + ",'" + Replace(rsProducts__BrandName, "'", "''") + "')}"
rsProducts.CursorType = 0
rsProducts.CursorLocation = 3
rsProducts.LockType = 1
rsProducts.Open()

rsProducts_numRows = 0
%>

CREATE PROCEDURE spProducts @SubCatID [int], @BrandName VARCHAR(255)=NULL AS
SELECT dbo.tblProdCategories.ProductID, dbo.tblProdCategories.SubCatID, dbo.tblProducts.BrandName, dbo.tblProducts.ProdCode, dbo.tblProducts.ProdName,
dbo.tblProducts.ProdDescription, dbo.tblProducts.UnitRRPrice, dbo.tblProducts.UnitPricegen, dbo.tblProducts.UnitPricemem, dbo.tblProducts.LineItemTax, dbo.tblProducts.DiscountedFlag, dbo.tblProducts.BestSellerFlag, dbo.tblProducts.PageFileName,
dbo.tblProducts.Discontinued, dbo.tblProducts.DateAdded
FROM dbo.tblProdCategories INNER JOIN
dbo.tblProducts ON dbo.tblProdCategories.ProductID = dbo.tblProducts.ProductID
WHERE dbo.tblProdCategories.SubCatID=@SubCatID AND dbo.tblProducts.Discontinued = 0 AND dbo.tblProducts.BrandName LIKE COALESCE(@BrandName, BrandName)
GO

jhermiz

3564 Posts

Posted - 2006-03-16 : 18:29:55

The spProducts takes a parameter of @BrandName, do you need that? You are asking us a question that you should be able to tell us whether or not you need that. What is it exactly (show us your data, sample data, any code, etc) that you want to do?
If it is just filling up the products combo box or grid or list then yes create another sproc that does not take the @BrandName parameter.

SELECT ProductID, Product FROM Product ORDER BY Product

Then fill your product combo box. Then to load the Brands of the product you write a sproc that goes into your other table and selects data based on the productid

SELECT BrandID, Brand FROM Brand WHERE ProductID = @ProductID

This will then filter the brands based on the Product.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

jamesnoony
Starting Member

2 Posts

Posted - 2006-03-17 : 06:49:17

Ok...
The SP for products all works fine and I do need the @BrandName

It would by default have a query string into the page such as
/products.asp?SubCatID=3

(At that point it is NOT using the @BrandName) and would pull a set of results such as

Sony ABC 123
Sony DSC 345
Sony BGT 123
Phillips 567
Phillips 789
Aiwa 678
Aiwa 836

But the idea is I would then have a drop down list that would have a list of distinct BrandNames (filtered from the SubCatID), ie the list would look like

Show All Brands
Sony
Phillips
Aiwa

The list would then post to the page with the querystring
/products.asp?SubCatID=3&BrandName=Sony

The results would then be just

Sony ABC 123
Sony DSC 345
Sony BGT 123

ie the @BrandName is an optional parameter in the SP

Hope that makes it a little clearer. Thanks, James

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources