Multiple Products separated by a comma

[paunescu]

Hi. I been trying to figure this out on my own, but seems I ran out of ideas.
I have a database like this:

Table Products:
ProductID
ProductName

Table Requirements:
ProductID
RequireID

Table Purchases:
ProductID
Confirm

I want to display each product with its requirements and if the required product was purchased or not. The problem I am facing is that the RequireID and ProductID in Requirements table are the same. Reason for it is that each product I get, I may or may not have to get another one in order for it to work properly, so I want to put a Requirement Product for each one, then display the product with ALL the required products under it, so that I know I have to add those products too, and those products shown with a Yes/No field next to it, basically tells me if I purchased it already or not. Not sure if I explained it right, but I hope so.
This is the display I want to show:

Product:
product XYZ

Requirements:
Product B Yes
Product H No
Product D Yes

Product:
product ABC

Requirements:
Product G No
Product S No
Product L Yes


Here are my statements:

SELECT DISTINCT Products.ProductName, Requirements.RequireID, Purchases.Confirm, Products.ProductID FROM Purchases INNER JOIN (Products INNER JOIN Requirements ON Products.ProductID = Requirements.ProductID) ON Purchases.ProductID = Products.ProductID

The Purchases.Confirm field is the one that has a Yes/No text field which tells if purchased or not. The Requirements.RequireID field has all the products that are needed for each product, separated with a comma (G, S, L, etc).

Here is the table setup:

<table width="100%" border="1" cellspacing="1" cellpadding="2">
<tr bgcolor="#0066FF">
<td width="50%"><div align="center">Name</div></td>
<td width="50%"><div align="right">Code</div></td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT Files.EOF))
%>
<% If Not Files.EOF Or Not Files.BOF Then %>
<tr>
<td><div align="center"><%=(Files.Fields.Item("ProductName").Value)%></div></td>
<td><div align="right"><%=(Files.Fields.Item("ProductID").Value)%></div></td>
</tr>
<tr>
<td colspan="2"><div align="right">Requirements:
<%=(Files.Fields.Item("RequireID").Value)%></div>
<div align="right"></div></td>
</tr>
<tr>
<td colspan="2"><div align="right"><%=(Files.Fields.Item("Confirm").Value)%></div></td>
</tr>
<tr bgcolor="#000000">
<td colspan="2">&nbsp;</td>
</tr>
<% End If ' end Not Files.EOF Or NOT Files.BOF %>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Files.MoveNext()
Wend
%>
</table>


I am trying to make it so that everytime one download takes place, I know I must purchase a different product so that first product will work.

Please let me know if I need to explain more or need anything else.

THANK YOU...

 

[Tarwn]

The simplest method is to keep track of the current product your outputting and only output the product name if it has changed, but output the requirement value on every loop.

The first missing thing I see is that your not pulling in the information you need from your Purchases table. You need to expand your query to include two copies of the product table, one for the product that your listing and one for the required product for that product. So the altered SQL statement should look something like:

SELECT Product.ProductID, Product.ProductName, RProduct.ProductID as RProductID, RProduct.ProductName as RProductName, Purchases.Confirm
FROM ((Product INNER JOIN Requirements ON Product.ProductID = Requirements.ProductID) 
   INNER JOIN Purchases ON Requirements.RequireID = Purchases.RequireID)
   INNER JOIN Product RProduct ON Purchases.ProductID = RProduct.ProductID
ORDER BY Product.ProductID, RProductName

Note: the immediate issue I have with this design is that you are assuming that all requirements are going to have an entry in Purchases whether or not they have been purchased yet. If this model is the final model then you pretty much don't need the Purchases table at all. You could simply add the Confirm field to the Requirements table (maybe rename it to "Purchased") and be done with it.


In any case, now you need to rework your loop. The simplest way to do this would be to create a variable to keep track of your last ProductID, then as you loop output a new header (product infomration) every time it changes, as had mentioned above. That would look something like:

Dim last_pid
Do Until MyRS.EOF
   If last_pid <> MyRS("ProductID") Then
      Response.Write "<br/>"
      Response.Write "Product: <br/> " & MyRS("ProductName") & "<br/>"
      Response.Write "<br/>Requirements:<br/>"

      last_pid = MyRS("ProductID")
   End If

   Response.Write MyRS("RProductName") & " " & MyRS("Confirm") & "<br/>"

   MyRS.MoveNext
Loop

The code will trigger the if statement on the first loop because last_pid will be empty. Each time it runs into a new ProductID it will output a new header. The rest of the time it will just output the requirement and whether it has been purchased or not. Obviously this is just an example and it's possible I may have even gotten some syntax errors in there, but the basic concept should be sound.

-T

 

[paunescu]

Tarwn, U are always the best. One little problem, which perhaps I may loverlooked.
I am getting an error:
Too few parameters. Expected 2.
/files.asp, line 13


That line is the one U gave me. I had to rewrite it a little, so match the tables up, but it should not give me that error.
I put the same query in Access, and one thing it does different it asks me for a product name.
Now if thats what I am missing, then my mistake. I dont put a product name in the list, I pretty much list all the products on that one page.

SELECT Products.ProductID, Products.ProductName, RProduct.ProductID AS RProductID, RProduct.ProductName AS RProductName, Purchases.Confirm
FROM Products INNER JOIN Requirements ON Products.ProductID = Requirements.ProductID, Purchases INNER JOIN Products AS RProduct ON Purchases.ProductID = RProduct.ProductID
ORDER BY Products.ProductID, RProductName;

Oh, and THANK YOU for the combining of the purchases, I would never been able to figure that out, less tables, less worries, lol. THANK YOU


THANK YOU VERY MUCH FOR YOUR TIME, and as always U are the best.

 

[Tarwn]

If Access asks you for a field value then that means the fieldname doesn't match up with one in your database. Check all of the fields in the SQL statement to make sure they are the same as the ones in your database. Heck I may have mispelled one or misread one from your post above

Also, I'd suggest not taking out the INNER JOIN between Requirements and Purchases. Inner Joins are generally a lot more efficient then comma-delimiting your tables because with an inner join you can put your constraints with the JOIN (the ON statement) whereas with a comma you have to put your constraints in the WHERE clause. The reason this is an isue is because it builds a temporary table in memory of all of the data in the FROM before it filters those records based on the WHERE. With the INNER JOIN's constraints you will have a lot fewqer records for it to filter through. The comma, on the other hand, basically builds a temporary table of every combination of the records on the left with the records on the right.
For example, if you have 5 requirements records and 5 purchases records andthey match one to one, then
FROM Requirements INNER JOIN Purchases ON Requirements.RequireID = Requirements.ProductID will generate 5 records
FROM Requirements, Purchases will generate 25 records

The key to INNER/OUTER JOINs is that it generates fewer records that will later have to be filtered or reordered.

Currently your SQL looks like:

SELECT Products.ProductID, Products.ProductName, RProduct.ProductID AS RProductID, RProduct.ProductName AS RProductName, Purchases.Confirm
FROM Products INNER JOIN Requirements ON Products.ProductID = Requirements.ProductID, Purchases INNER JOIN Products AS RProduct ON Purchases.ProductID = RProduct.ProductID
ORDER BY Products.ProductID, RProductName;

You need to either add the INNER JOIN back in, or add a WHERE statement to match up the RequireID from Requirements to the ProductID in Purchases, otherwise your going to geta lot of records where the purchase of some random product i matched to another product that it isn't actually a requirement of.

-T

 

[paunescu]

Tarwn, i re-read the statement, re-copied it and all I did is add the "S" in product. I looked over the statement over and over like crazy, and just cant figure out why its not working, nor why is asking for that field name.
This is what I got. Sorry, just cant see where the problem is.


This is the statement currently in sql view:


SELECT Products.ProductID, Products.ProductName, RProduct.ProductID as RProductID, RProduct.ProductName as RProductName, Purchases.Confirm
FROM ((Products INNER JOIN Requirements ON Products.ProductID = Requirements.ProductID)
INNER JOIN Purchases ON Requirements.RequireID = Purchases.RequireID)
INNER JOIN Products RProduct ON Purchases.ProductID = RProduct.ProductID
ORDER BY Products.ProductID, RProductName

The tables are as follows:
Table Products:
ProductID
ProductName

Table Requirements:
ProductID
RequireID

Table Purchases:
ProductID
Confirm


When I put it in MS Access, I get asked to enter something in field:
RProductName (says enter a value).

The when I do it as web asp, I get that expected error.

Now I removed the Order by completely, and no more errors, but again that would not work, as it brings ALL the records with every required data in each one, weather if it was required or not, it shows it as required.

Gonna keep messing with the statement, but I am going nuts here, cant figure out why it wont work, it should as U said, but its weird, I checked and double checked for mis-types, database field names, etc etc, and still cant find the problem area.

ANY more help on this I would greatly grealy appreciate it VERY MUCH.

THANK YOU VERY VERY MUCH...

 

[Tarwn]

Hmm, it may not like the RProductName in the ORDER BY. Try replacing that with RProduct.ProductName. I always forget whetehr you can use aliased fields in the ORDER BY and I always get it wrong

 

[paunescu]

Thank you Tarwn, that got rid of the error, but I am sorry to bother again, no records are being shown at all. I have added test products properly to each table, and still NOTHING comes out. I try it from Ms Access, Dreamweaver and IE, but no luck, no records what so ever.
The records are not being pulled from the statement. It does not even touch the reponses.writes, just no records at all.

Here is the statement again, with all the necessary tables. Sorry to bother again, just I am really stuck on this one. I can put the database online and let U download it and see if U so desire.

Thank You...



SELECT Products.ProductID, Products.ProductName, RProduct.ProductID as RProductID, RProduct.ProductName as RProductName, Purchases.Confirm
FROM ((Products INNER JOIN Requirements ON Products.ProductID = Requirements.ProductID) INNER JOIN Purchases ON Requirements.RequireID = Purchases.ProductID) INNER JOIN Products RProduct ON Purchases.ProductID = RProduct.ProductID
ORDER BY Products.ProductID, RProduct.ProductName

 

[Tarwn]

You may get better results if you switch some of the JOINs to Left joins rather than inner joins. An inner join requires records in both tables in order to return a single record, a left join only requires records in the left table (and returns nulls in the fields for the second table if there weren't any matching records).

Try this one:

SELECT Products.ProductID, Products.ProductName, RProduct.ProductID as RProductID, RProduct.ProductName as RProductName, Purchases.Confirm
FROM ((Products LEFT JOIN Requirements ON Products.ProductID = Requirements.ProductID)      LEFT JOIN Purchases ON Requirements.RequireID = Purchases.ProductID)     LEFT JOIN Products RProduct ON Purchases.ProductID = RProduct.ProductID
ORDER BY Products.ProductID, RProduct.ProductName

Wherever the nulls start showing up in the returned rcords is where you don't have matching records in the table for that section.

-T