Nested loop - Page 3

Let's loose all the stupid loops.

Oh please tell me you "Dim LastTopCat" at the top of the page right?

Try this:

If Not RS.EOF Then
	Response.Write("<table width=""570"" border=""1"">")
	TopCat = RS("topcategory")
	MiddleCat = RS("middlecategory")
	LastTopCat = ""
	i = 3

	Do While Not RS.EOF
		If i = 3 Then
			Response.Write("<tr>")
			i = 0
		End If

		If TopCat <> LastTopCat Then
			Response.Write ("<td width=""190"">")
			Response.Write(TopCat & "<br />")
			Response.Write("-----------------------------")
			LastTopCat = TopCat
		End If

		If TopCat = LastTopCat Then
			Response.Write(MiddleCat)
		End If

		RS.MoveNext

		If TopCat <> RS("topcategory") Then
			TopCat = RS("topcategory")
		End If

		MiddleCat = RS("middlecategory")

		If TopCat <> LastTopCat Then
			Resposne.Write("</td>")
			i = i + 1
		End If

		If i = 3 Then
			Response.Write("</tr>")
		End If
	Loop

	Response.Write("</table>")
End If

This is what it hopefully will looks like

This is faked table

Yes, I did now. But some error there is.

There is two recordsets available, one for topcats and one for middles

One by each SQL query
RS and RS1

wait, you didn't declare the variable before? Run the last code where you said it looks good.

The reason why it kept skipping over is that it didn't keep LastTopCat in memory, therefore when it compared, it compared TopCat to nothing, hence failing.

Dim LastTopCat at the top of the page and try the 2nd to last code I gave you.

Nope, nothing at all, All variables are dimmed

I don't know then. It should all work just fine, especially with the 2nd to last code I gave you. I mean, it looped through all the top cats, caught the middles, then went on. Now nothing at all?

The only wrong I can find was Resposne.Write and changed that to Response.Write

Hang on.....

I reloaded it a couple of times and this is the output

And row 47 is this one
If TopCat <> RS("topcategory") Then

Not the code on this page, the code on page 6, the second to last code I sent you.

This one:

If Not RS.EOF Then
	Response.Write("<table width=""570"" border=""1"">")

	Do While Not RS.EOF
		Response.Write("<tr>")
		i = 0
		TopCat = RS("topcategory")
		MiddleCat = RS("middlecategory")

		Do While i <> 3
			If Not RS.EOF Then
				If Not LastTopCat = TopCat Then
					Response.Write ("<td width=""190"">")
					Response.Write(RS("topcategory") & "<br />")
					Response.Write("-----------------------------")
					LastTopCat = TopCat
				End If
					
				Response.Write("<br />" & MiddleCat)

				RS.MoveNext

				If Not RS.EOF Then
					TopCat = RS("topcategory")
					MiddleCat = RS("middlecategory")
				End If

				If Not LastTopCat = TopCat or RS.EOF Then
					Response.Write("</td>")
					i = i + 1
				End If
			Else
				Response.Write("<td width=""190""></td>")
				i = i + 1
			End If
		Loop

		Response.Write("</tr>")
	Loop

	Response.Write("</table>")
End If

That one (on page 6) is also dimmed and that one is working in the matter of 1 topcat and 1 middlecat

The lovely good ole days of Classic ASP.

No reason why something shouldn't work, but there's the error.

Yep, I can agree with that

The reason why I split the SQL query into 2 queries was the repeating issue. You will only get the first middlecat along with each topcategory within the query (SQL). The (SQL1) combined with (SQL) was meant as a workaround to pick out every middles connected to each topcat without the need of repeating topcats or middlecats.

Maybe the solution is to optimize the SQL !? I don't know?

SQL = "SELECT DISTINCT TC.topcategory,MC.middlecategory,CC.middlecategorylink AS MCLink "&_
"FROM tbtopcategory TC,tbmiddlecategory MC,tbconnectcategory CC "&_
"WHERE TC.topcategoryID = CC.topcategorylink "&_
"AND MC.tbmiddlecategoryID = CC.middlecategorylink " &_
"GROUP BY TC.topcategory "
set RS = Server.CreateObject("ADODB.recordset")
RS.Open SQL,Conn

SQL1 = "SELECT middlecategory " &_
"FROM tbmiddlecategory WHERE tbmiddlecategoryID = '" & RS("MCLink") & "'"
set RS1 = Server.CreateObject("ADODB.recordset")
RS1.Open SQL1,Conn

Oh brother, try this code and then I will tell you what's wrong.

If Not RS.EOF Then
	Response.Write("<table width=""570"" border=""1"">")

	Do While Not RS.EOF
		Response.Write("<tr>")
		i = 0
		TopCat = RS("topcategory")
		MiddleCat = RS("middlecategory")

		Do While i <> 3
			If Not RS.EOF Then
				If Not LastTopCat = TopCat AND Len(TopCat) > 3 Then
					Response.Write ("<td width=""190"">")
					Response.Write(RS("topcategory") & "<br />")
					Response.Write("-----------------------------")
					LastTopCat = TopCat
				End If
					
				Response.Write("<br />" & MiddleCat)

				RS.MoveNext

				If Not RS.EOF Then
					TopCat = RS("topcategory")
					MiddleCat = RS("middlecategory")
				End If

				If (Len(TopCat) > 3 AND TopCat <> LastTopCat) or RS.EOF Then
					Response.Write("</td>")
					i = i + 1
				End If
			Else
				Response.Write("<td width=""190""></td>")
				i = i + 1
			End If
		Loop

		Response.Write("</tr>")
	Loop

	Response.Write("</table>")
End If

Your databases aren't connected correctly. Your topcategory and middlecategory should be connected like this:

TopCat
- TopCatID
- TopCatName

MiddleCat
- MiddleCatID
- TopCatID
- MiddleCatName

They aren't, and because of that you are pulling empty values.

When you pull the TopCat Name, you prolly are able to pull 1 value with it.

You need a database restructure.

Works with the usual 1 topcat 1 middlecat.

Yes it's what your database is pulling, not how the code handles it. You need a database redesign/restructure to better relate the data.

If it requires you for two queries to grab the information, then the information isn't related correctly.

If you want to see what I mean, run this code:

If Not RS.EOF Then
  Do Until RS.EOF
    Response.write(RS("topcategory") & " ~ " & RS("middlecategory") & "<br />")
    RS.MoveNext
  Loop
End If

The connecting table (tbconnectcategory) which holding the foreign keys looks like this:

TbConnectCategory
- topcategorylink <> tbtopcategoryID
- middlecategorylink <> tbmiddlecategoryID

That should be enough as long as keys are unique and the query is intelligent enought (he he), depends who the writer is. Maybe I should rewrite my SQLs

MAN you are absoluteley right.

As talked about before (GROUP BY) cluse was set to top category. I deleted that one and VOILA

Hang on

CHECK THE ATTACH - IT IS NOT A FAKE IT'S FOR REAL

Oh okay that's how you have it set. Yeah that is fine. Not highly recommended, but it works. Okay then try this query:

SQL = "SELECT TC.topcategory, MC.middlecategory "&_
"FROM tbtopcategory TC, tbmiddlecategory MC, tbconnectcategory CC "&_
"WHERE TC.topcategoryID = CC.topcategorylink "&_
"AND MC.tbmiddlecategoryID = CC.middlecategorylink " &_
"GROUP BY TC.topcategory "

You have to lost the DISTINCT, otherwise you will only grab One Top Category, which means you only grab one MiddleCategory. Drop the destinct, and no need for the MCLink.

And now your thing should work.

NO NO, you have to keep GROUP BY clause in there, if you don't, your records won't go in the right places, and you'll have duplicate columns!

You just need to lose the DISTINCT. DISTINCT grabs only the unique ones. So if you have 10 TopCats named NameMyTopCat, it will only pull 1. Since it only pulls 1, it will only pull one MiddleCat. That's where you failed.

Yes this was the right one, added middlecat as well to GROUP BY and it is working

SQL = "SELECT TC.topcategory, MC.middlecategory "&_
"FROM tbtopcategory TC, tbmiddlecategory MC, tbconnectcategory CC "&_
"WHERE TC.topcategoryID = CC.topcategorylink "&_
"AND MC.tbmiddlecategoryID = CC.middlecategorylink " &_
"GROUP BY TC.topcategory ASC, MC.middlecategory ASC"
set RS = Server.CreateObject("ADODB.recordset")

You probably don't even need middlecategory. If you wish, you should do it this way:

SQL = "SELECT TC.topcategory, DISTINCT(MC.middlecategory) "&_
"FROM tbtopcategory TC, tbmiddlecategory MC, tbconnectcategory CC "&_
"WHERE TC.topcategoryID = CC.topcategorylink "&_
"AND MC.tbmiddlecategoryID = CC.middlecategorylink " &_
"GROUP BY TC.topcategory"

This way it will grab only the unique sub names (which you don't want duplicates of anyway). No need for group by, as when you have uniques, grouping 1, 2, 3 will turn out as 1, 2, 3. It's pointless.

Glad to see its working. Show me? :)

I don't how to thank you enough for the most valued and professional help any can ever get on any forum.

This is the output (before I prints it out and hang it in some golden frame)

You probably don't even need middlecategory. If you wish, you should do it this way:

SQL = "SELECT TC.topcategory, DISTINCT(MC.middlecategory) "&_
"FROM tbtopcategory TC, tbmiddlecategory MC, tbconnectcategory CC "&_
"WHERE TC.topcategoryID = CC.topcategorylink "&_
"AND MC.tbmiddlecategoryID = CC.middlecategorylink " &_
"GROUP BY TC.topcategory"

This way it will grab only the unique sub names (which you don't want duplicates of anyway). No need for group by, as when you have uniques, grouping 1, 2, 3 will turn out as 1, 2, 3. It's pointless.

Glad to see its working. Show me? :)

Next time, remind me to look at the query first lol.

You're welcome.

Dont hesitate to contact me if there is anything I can do for help. I will be here now and then.

Again thanks and many greetings from Stockholm

sincerely
Torbjorn