distinct record-sql-asp 3

[ramonzulueta]

Hi. Good day, can you please give me some direction on my SQL statement using asp.

If you have got a table with some data that look something like this:

id field
1 a
2 a
2 b
3 c
4 a
4 b
4 c

How do you make it look like this(what would be the sql statement for this please):
id field
1 a
2 a,b
3 c
4 a,b,c

 

[Sheco]

Do you want to use ASP to create an HTML table that looks like this?

 

[ramonzulueta]

sorry if i have confused you. this bit is from my access database

id field
1 a
2 a
2 b
3 c
4 a
4 b
4 c

and i want to have a recordset that would give me something like this:

id field
1 a
2 a,b
3 c
4 a,b,c

 

[Sheco]

You want it that way inside an ADO recordset object or you want an ASP that displays it this way on the browser?

 

[ramonzulueta]

that's inside an ado recordset please

 

[ramonzulueta]

for example, for id=2 i want to pull 'a,b' from the database and write a,b onto an html page using asp according to the value of 'id'

 

[Sheco]

If all you ultimately want to do is write an HTML page then you dont really need a hierarchical recordset if you do something like this:

Dim ThisId, LastId
Do While Not rs.EoF
ThisId = rs("id")

IF (ThisId = LastId) THEN
'still on the same row
Response.Write "," & rs("field")
ELSE
'start a new row
Response.Write "<BR>" & rs("id") & "&nbsp;&nbsp;&nbsp;" & rs("field")
END IF

LastId = ThisId
rs.MoveNext
Loop

 

[PaulBricker]

You can use this to set it up the way you want.

<%
Dim strQuery, objConn, dbPath, strProvider
Dim myval, myfld

strQuery = "Select * From tblYourTable"
dbPath = "C:\SomeFolder\SomeDatabase.mdb"
Set objConn = Server.CreateObject("ADODB.Connection")
strProvider = "Provider = MICROSOFT.Jet.OLEDB.4.0;Data Source=" & dbPath

objConn.Open strProvider

Set rst = Server.CreateObject("ADODB.recordset")
   rst.Open strQuery, strProvider

     rst.MoveFirst
       myval = rst("ID")
       myfld = rst("Field")
     rst.MoveNext
       Do Until rst.EOF
        If rst("ID") <> myval Then
          response.write "<br>" & myval & "&nbsp;&nbsp;" & myfld
          myval = rst("ID")
          myfld = ""
        Else

       Do While Not rst.EOF
        If rst("ID") = myval Then
          myfld = myfld & rst("Field")& ","
          rst.MoveNext
        Else
          response.write "<br>" & myval & "&nbsp;&nbsp" & Left(myfld, Len(myfld)-1)
          Exit Do
        End If
       Loop 
        If rst.EOF Then
          response.write "<br>" & myval & "&nbsp;&nbsp;" & Left(myfld, Len(myfld)-1)
        Else
         myval = rst("ID")
         myfld = ""
        End IF
       End If
      Loop
Set objConn = Nothing
Set rst = Nothing
%>

This should return the data the way you want. One thing, the data will have to have ID's grouped together. It won't handle ID's that are spread throughout the recordset so you may have to put the info in a query, sort it there and reference the query in strQuery.


Paul

 

[ramonzulueta]

it worked! thanks Sheco & PaulBricker for your invaluable help. cheers!!

 

[ramonzulueta]

Hello Sheco & Paulbricker, I am very sorry if I will ask you again. it's has something to do again with the question i have posted here. it's ok, it's working. but can you give me an insight if i have a table:

main_table1
id somefield
1 apple
2 grapes
3 orange
4 pear

main_table2
id somefield
1 mobile
2 phone
3 car
4 bike

link_table
id(main_table1) field(id of main_table2)
1 2
2 2
2 3
3 1
4 2
4 4
4 3

what i wouldlike to achieve is using the code you gave me, i would like to have a result, according to the "id" selected by the user, something like:

e.g.
if user selected id=1 (from link_table)it will show "mobile"
if user selected id=2 (from link_table) it will show "phone, car"
if user selected id=3 (from link_table)it will show "mobile"
if user selected id=4 (from link_table)it will show "phone, bike, car"

thanks very much indeed and hoping for your reply. cheers

 

[steven290]

you can create your sql like this

sql="select link_table.id,main_table2.somefield from link_table "&_
"left join main_table2 "&_
"on main_table2.id=link_table.field "&_
"where link_table.id="&youidvar

 

[PaulBricker]

I'm not sure why you would need a Left Join. This sql returns the info you want.

"SELECT Link_Table.id, Main_Table2.somefield, Link_Table.field
FROM Link_Table INNER JOIN Main_Table2 ON Link_Table.field = Main_Table2.id
WHERE Link_Table.id = " & youridVar"

Then you would have to open the recordset and loop thru to pick up your values in the format you want using some of the code from above.

Paul

 

[steven290]

i used left join because if the Link_Table.field is blank but the Link_Table.id = " & youridVar" matches it would still return the record

with inner it would exclude it

 

[PaulBricker]

OK, I can follow that logic. I'm not sure if you would want the blank record, but it does give them some options I hadn't thought of.

Paul

 

[ramonzulueta]

Hello PaulBricker & steven290, i cant thank you enough for all your insights and help. cheers!

 

[steven290]

glad it worked out for you