Multiple queries into one 1

[tyutghf]

I have a table that displays data from a database, ordered by their category. For this I have a category field in the database, however now each item can now live under multiple categories (up to 3 max).

Is it possible to query the database once to look through all category rows and output one set of results? At the moment I am looking through category, outputting results, then loop through category2 then again for category3.

The categories are controlled in another table, hence the join below.

SQL = "SELECT products.productid, products.productcategory, products.productname, productscategory.productcategory FROM products INNER JOIN productscategory ON products.productcategory=productscategory.id where products.removed is null order by products.productcategory" 
set rs = server.CreateObject("ADODB.RECORDSET")
rs.open SQL,objConn

if not rs.EOF then
do while not rs.EOF
%>
<tr valign="top">
<td><%=rs("productcategory")%></td>
<td><%=rs("productname")%></td>
</tr>

//then again for category2.

My table in the DB is like

id | category | category2 | category3 | productname | etc etc

 

[MarkXaml]

Sure, you can union the results. Try something like this:

SQL = "SELECT products.productid, products.productcategory, products.productname, productscategory.productcategory FROM products INNER JOIN productscategory ON products.productcategory=productscategory.id where products.removed is null"

SQL = SQL & " UNION SELECT products.productid, products.productcategory, products.productname, productscategory.productcategory FROM products INNER JOIN productscategory ON products.productcategory2=productscategory.id where products.removed is null"

SQL = SQL & " UNION SELECT products.productid, products.productcategory, products.productname, productscategory.productcategory FROM products INNER JOIN productscategory ON products.productcategory3=productscategory.id where products.removed is null order by products.productcategory"

note you cannot order by until the last union.

Good luck!

Mark

 

[tyutghf]

Didn`t know about UNION, that is brilliant thank you. That will come in very handy, in fact I know another script where I can go use it right now ;o)

 

[gmmastros]

Since you didn't know about union, you probably don't know about union all either.

UNION will filter the data amongst and between the queries and only return the distinct rows. UNION ALL will allow duplicates. DISTINCT is a relatively expensive operation in terms of query performance. If you can avoid it, you should. If your data will naturally be distinct, then you should use union all instead of union.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom