Difficult Select, selecting from another table.

[snowboardr]

I have a situation, I didn't plan for this at the start of my design.

I know of a few ways to do this, but was wondering if there is a way I could do it with a complex select statement or something.

I have a user table, and one column in that user table has id values in this patern 1, 2, 3, 4 etc...

this is so I could do a select Select from userrecords WHERE id In(1, 2, 3, 4)

Well what I would like to do is write out all my users on the page, and write the name of each record that that 'id list' represents.

What do you think would be the best way to do this?

- Jason




www.vzio.com
ASP WEB DEVELOPMENT

 

[spazman]

select * from usertable, then you will have acces to all the fields so you can write it out to screen

 

[Mr3Putt]

I'll assume that you're able to create your connection object, recordset object, and dim your variables, etc...

sqlStmt = "select id, firstName, lastName from myTable order by 3, 2"

set rs = conn.Execute(sqlStmt)

if rs.EOF and rsBOF then
  Response.Write("No Records in Table!")
  set rs = nothing
  conn.Close : set conn = nothing
else
  rsArray = rs.getRows
  rs.Close : set rs = nothing
  conn.Close : set conn = nothing
  rsRows = UBound(rsArray, 2)

  for rn = 0 to rsRows
    Response.Write(rsArray(0,rn) & &quot;: &quot; & rsArray(1,rn) & &quot;, &quot; & rsArray(2,rn) & &quot;<br>&quot;)
  next
end if

That should produce a list similar to:

2: Cheatam, Larry
1: Dewey, Moe
3: Howe, Curly

 

[snowboardr]

Im going to have to explain myself better I guess.

Tables layout

|-users-|
enrolledclasses

|-classes-|
cid
classname


In the users table enrolledclasses would contain a bunch of Class id's in a list like 1, 2, 3 etc

I need to output it like this:

user name ... other user information
classes enrolled into: classname1, classname2

But I need to do it as part of the user select while still getting the class names

sounds a bit difficult to me but I just can't think of a solution off the bat.

- Jason

 

[Shaggs]

why didn't you make a link table
UsersEnrolledClass containing UserID, ClassID.

Storing delimeted data in a database doesn't make any sense.
I don't mean to be rude of course.

 

[snowboardr]

Well I could have done it that way, but this app was back in the day... I may redesign it, but for now I would like to find a quick fix.

www.vzio.com
ASP WEB DEVELOPMENT

 

[shaddow]

You can fix it.
I used this way also and that time was the best way.
So you store in this field enrolledclasses all of your id's from the second table.
First of all i would use 2 recordsets.
also i presume that
enrolledclasses field contains the id's separated by comma
"1,3,4,6" for example.

set rs=conn.Execute("select * from users&quot
while not rs.Eof
set rsclass=con.Execute("select * from classes where id in ("&rs("enrolledclasses&quot&&quot"
'do your job

wend

This should do the job if not it's not easy to make a script to temporary add in a table all your userid <-> class conexions and then rename that table or just use the other table for querryes.
Either way should work ok on a regular web site or intranet where speed it's not the main matter.

________
George, M

 

[shaddow]

Heh, i forgot to mention few things but i think you got the ideea

set rs=conn.Execute(&quot;select * from users&quot
while not rs.Eof
set rsclass=con.Execute(&quot;select * from classes where id in (&quot;&rs(&quot;enrolledclasses&quot&&quot&quot;
while not rsclass.Eof
'do your job
rsclass.MoveNext
wend
rs.MoveNext
wend


________
George, M

 

[DaveGerard]

This will allow you to see all users and their corresponding classes. It will display the user only once and nest the classes underneath the user in a separate row. When the UserName Changes the next user will display (but only once). Let me know if that helps.

<%
db = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=whatever.mdb&quot;

set rs = server.createobject(&quot;adodb.recordset&quot
sql = &quot;select Users.EnrolledClasses, Users.UserName, Classes.Cid, Classes.ClassName&quot;
sql = sql & &quot; from Users&quot;
sql = sql & &quot; INNER JOIN Classes ON Users.EnrolledClasses = Classes.Cid&quot;
sql = sql & &quot; order by Users.UserName&quot;
rs.open sql, db
if not rs.eof then
do while not rs.eof

if UserPass <> rs(&quot;UserName&quot then
%>

<tr>
<td width=&quot;100%&quot; colspan=&quot;2&quot;><%=rs(&quot;UserName&quot%></td>
</tr>
<tr>
<td width=&quot;50%&quot;>Class ID</td>
<td width=&quot;50%&quot;>Class Name</td>
</tr>

<%
end if
%>

<tr>
<td width=&quot;50%&quot;><%=rs(&quot;EnrolledClasses&quot%></td>
<td width=&quot;50%&quot;><%=rs(&quot;ClassName&quot%></td>
</tr>

<%
UserPass <> rs(&quot;UserName&quot
rs.movenext
loop

end if
rs.close
set rs = nothing
%>