Distinct output using ASP/SQL

I am very close to being done with this project but I could use some help with this last part. I have two SQL tables and an ASP page. I am simply reading those tables and displaying data based on ID number. The output is names and links. I have been successful in displaying the names, their descriptions and the links and their descriptions. However, if I have three distinctly different names and three distinctly different links, when I test it I see the three names and their descriptions no problem, but the it shows all three links for all three names.

Meaning for example:

John Doe
Sue Smith
Mike Wilkens

* John's House of Pancakes (this is the link specific to only John Doe)
* Sue is the owner of the new Seafood Restaurant (this is the link specific to only Sue Smith)
* Mike is the author of the new book (this is the link specific to only Mike)

So what happens is I see John's name and all three of the links then Sue's name and all three of the links and so on. I only want to see one link per name and that link needs to be the one specific to that name. See below my SQL and ASP code.

<%
set con = Server.CreateObject("ADODB.Connection")
con.Open "File Name=E:\webservice\Company\Company.UDL"
set rs = Server.CreateObject("ADODB.Recordset")

id=request.querystring("id")

IF id <> "" then id=id else id="9734" end if

'  This will apparently pull back too many records
strSQL = "SELECT DISTINCT GuestName, GuestDescription, URL, Description FROM T_ProgramGuests tpg LEFT JOIN T_ProgramLinks tpl ON tpl.ProgramID = tpg.programID WHERE (tpg.ProgramID = " & id & ")" 

rs.Open strSQL,con

previousGuestName = ""

While Not RS.Eof
	'  Only output guest details if this record has a different guest name to the previous one
	if previousGuestName <> cStr(rs("guestName")) then
		response.write "<br />" & vbcrlf
		response.write "<strong>" & rs("GuestName") & "</strong>"  & rs("GuestDescription") & "<br /><br />Related Links:<br />"  & vbcrlf
		previousGuestName = cStr(rs("guestName"))
	end if

	'  Always output the related links though
	response.write "<li class=""basic""><A HREF=""" & rs("URL") & """>" & rs("Description") & "</A></li>" & vbcrlf
	RS.MoveNext
Wend

recProgram.Close
con.Close
set recProgram = nothing
set con = nothing
%>

Code (markup):

Now the Display Code:

previousGuestName = ""

While Not RS.Eof
	'  Only output guest details if this record has a different guest name to the previous one
	if previousGuestName <> cStr(rs("guestName")) then
		response.write "<br />" & vbcrlf
		response.write "<strong>" & rs("GuestName") & "</strong>"  & rs("GuestDescription") & "<br /><br />Related Links:<br />"  & vbcrlf
		previousGuestName = cStr(rs("guestName"))
	end if

	'  Always output the related links though
	response.write "<li class=""basic""><A HREF=""" & rs("URL") & """>" & rs("Description") & "</A></li>" & vbcrlf
	RS.MoveNext
Wend

Code (markup):

So what am I missing in my code?

 

I would start with your MSSQL statement, look at what records that's bringing back. In particular, look at replacing the LEFT JOIN with an INNER JOIN (Just a hunch)
Cheers
Markus

 

are there any other fields in your table?

 

Ludwig -

Yes in the Guest table there is GuestID and ProgramID. When I test for this in my browser I am referencing the ProgramID.

Example: pagename.asp?ID=9734

The Links table has LinkID and ProgramID. Also to answer the other person's reply I tried Inner Join before and it was suggested to me in a forum to use Left Join instead.

Now what?

 

let me do it like this, you need distinct data from 2 different tables or just one which is connected to the second one?

if the option is the second one its easy for you. You select desctinct from table1 then select what you need based on the programID from table2

 

There are links that are specific to a certain person. Based on that wouldn't that mean I would need distinct data from two different tables? I'm just trying to avoid showing John Doe's link under Henry James for example. John and Henry should have their own unique links.

So how difficult would that be?

 

I'm noticing a minor thing when I view the output. There are spaces between each of the links. There shouldn't be and I don't see anything on this line that looks like spacing code. Unless I'm missing something in this line:

response.write "<li class=""basic""><A HREF=""" & rs("URL") & """>" & rs("Description") & "</A></li>"

Code (markup):

Would the extra double quotes cause spaces between links?

 

first of all you can have a USERID column in all your table and select by that ID and not a DISTINCT

next, here it is easier for you to write like this

so you can see are there spaces or no, view the HTML

 

Ludwig -

Cool I tried that but there are still spaces between the links. Again this is water under the bridge. I'm just trying to clean it up a bit. I have another question. This is in regards to ID # 9734

Why would there "not" be a space between Walter Mebaine's description and Bryan Jones? This outputs this way when I test.

Walter Mebane professor of government at Cornell University and a member of the DNC task force looking into the results in Ohio in 2004
Bryan Jones Donald R. Matthews Distinguished Professor of American Politics and director of the Center for American Politics and Public Policy

Related Links:
# 'Still Angry, Kerry Supporters Nationwide Try to Change Ohio Elections'
# 'Steal this Vote,' Andrew Gumbel interview by Julian Brookes, Mother Jones
# 'Democrats Say 2004 election failed in Ohio'

 

When I test ID 9785 I see this...

Tamara Murphy executive chef and owner of Brasa
Greg Atkinson food writer, chef and regular contributor to the Cooking Klatch
Tom Douglas chef and owner of many restaurants...

Related Links:
# Brasa
# Greg Atkinson

The section below is my stuff. Everything above is the normal stuff.

Greg Atkinson food writer, chef and regular contributor to the Cooking Klatch

Related Links:
# Brasa
# Greg Atkinson
# Tom Douglas Restaurants

Tamara Murphyexecutive chef and owner of Brasa

Related Links:
# Brasa
# Greg Atkinson
# Tom Douglas Restaurants

Tom Douglaschef and owner of many restaurants...

Related Links:
# Brasa
# Greg Atkinson
# Tom Douglas Restaurants

Each name in "my section" should have "only" one link not all three and the link should be associated with the name. Meaning...

The Brasa link is specific to Tamara
The Greg link is specific to Greg
The Tom Douglas link is specific to Tom

So what am I doing wrong that would cause this in my output?

 

I have learned that there is a programmatic way to remove spaces by using IsLetter method followed by the Replace method. Except that these are only supported in .Net and we are working with ASP 3.0. There must be a 3.0 equivalent.

Does anyone know of any?

 

I found this just moments ago online. Thoughts anyone? Will this work with my code?

void trim2(string& str)
{
  string::size_type pos = str.find_last_not_of(' ');
  if(pos != string::npos) {
    str.erase(pos + 1);
    pos = str.find_first_not_of(' ');
    if(pos != string::npos) str.erase(0, pos);
  }
  else str.erase(str.begin(), str.end());
}

Code (markup):

Source: http://www.codeproject.com/vcpp/stl/stdstringtrim.asp

 

I need help with this part folks. Based on the text below, if you wanted to see "only" the 2nd link with Andrew Gumbel and "not" all three links how would I need to trim this out in my code?

Andrew Gumbel correspondent for the Independent of London and the author of Steal This Vote: Dirty Elections and the Rotten History of Democracy in America

Related Links:
* 'Still Angry, Kerry Supporters Nationwide Try to Change Ohio Elections'
* 'Steal this Vote,' Andrew Gumbel interview by Julian Brookes, Mother Jones
* 'Democrats Say 2004 election failed in Ohio'

<%
set con = Server.CreateObject("ADODB.Connection")
con.Open "File Name=E:\webservice\Company\Company.UDL"
set rs = Server.CreateObject("ADODB.Recordset")

id=request.querystring("id")

IF id <> "" then id=id else id="9734" end if

'  This will apparently pull back too many records
strSQL = "SELECT DISTINCT GuestName, GuestDescription, URL, Description FROM T_ProgramGuests tpg LEFT JOIN T_ProgramLinks tpl ON tpl.ProgramID = tpg.programID WHERE (tpg.ProgramID = " & id & ")" 

rs.Open strSQL,con

previousGuestName = ""

While Not RS.Eof
	'  Only output guest details if this record has a different guest name to the previous one
	if previousGuestName <> cStr(rs("guestName")) then
	  response.write "<br />"
		response.write "<strong>" & rs("GuestName") & "</strong>"  & rs("GuestDescription") & "<br /><br />Related Links:<br />"
		previousGuestName = cStr(rs("guestName"))
	end if
	
	'  Always output the related links though
	response.write "<li class=""basic""><A HREF='" & trim(rs("URL")) & "'>" & trim(rs("Description")) & "</A></li>"
	strLine = Trim(strLine)
	If Len(strLine) > 0 Then
	strNewContents = strNewContents & strLine & vbCrLf
	end if
	RS.MoveNext
Wend

recProgram.Close
con.Close
set recProgram = nothing
set con = nothing
%>

Code (markup):