retrieving data from a many-to-many relationship, using (classic) ASP

[relixx]

It's strange, All this time working on various ASP projects, I've never actually to retrieve data from 2 tables that have a juntion table inbetween :eek: I mean, I've designed databases that used them, I've just never had to actually implement them :/

Basically, I have to the following 3 tables:

Employee
emp_id [pk]
emp_name

Project
proj_id [pk]
proj_name

emp_proj <-junction table
emp_id
proj_id

Basically, I have to display an HTML table that shows what projects each employee is working on. Obviously (as I wouldn't be asking this question if it weren't the case :P ), more than employee can work on more than one project at a time, and a project can have more than one employee assigned to it.

The horizontal row at the top of the table displays the project name, the vertical row on the very left displays the employee name, and each cell running across has an 'x' in it if the employee is working on that project.

The thing I'm stuck on is how to retrieve the information from all 3 tables and display it all. Any suggestions?

[vectorgraphx]

you'd want to use inner joins. For multiple tables, you can nest inner joins. for example:

selectsql = "select EMPLOYEE.emp_ID emp_ID, EMPLOYEE.emp_name emp_name, PROJECT.proj_ID proj_ID, PROJECT.proj_name proj_name from EMPLOYEE inner join (PROJECT inner join EMP_PROJ on PROJECT.proj_id = EMP_PROJ.proj_id) on EMPLOYEE.emp_ID = EMP_PROJ.emp_ID"

Set EMP_rs = Server.CreateObject("ADODB.Recordset")
EMP_rs.Open selectsql , your_db_connection,3,3


now, just call the variables by their names that you defined in the sql, i.e.:

<%=EMP_rs("emp_name")%> - <%=EMP_rs("proj_name")%> etc.

Let me know if that helps!

VG

[relixx]

Yeah it did, thanks :)

[vectorgraphx]

no problemo - glad it worked for ya :D