sql statement doubt

thas123 · February 7th, 2006, 06:12 AM

Hi,

How do you write sql statements for joining two tables
say

[u]Program</u>
PID
PNAME
DESC

[u]STUDENTS</u>
SID
SNAME
EMAIL
PWD
PID

how do i join these two tables using sql statements.here PID is the unique key among them.I know this statement.
select * from program,students where program.PID=students.PID

but my question is how do i use join statements for this.
I only want to display Pname from program table and every fields from students table.

and after creating sql statement how do i use datareader to access these values from two tables.
Normally i do for one table like this

while dr.read

response.write(dr("SNAME").tostring())
response.write(dr("SID").tostring())
response.write(dr("EMAIL").tostring())
response.write(dr("PWD").tostring())
end while

but in this case where i am joining two tables,how do i use dreader to do that.
ie.
response.write(dr("?").tostring())--??? what is it.
Please give me an answer as soon as possible

U.N.C.L.E. · February 8th, 2006, 05:33 AM

The SQL to join (INNER JOIN) the two related tables is:

Code:

SELECT STUDENTS.SID, STUDENTS.SNAME, STUDENTS.EMAIL, STUDENTS.PWD, STUDENTS.PID, PROGRAM.PNAME
FROM STUDENTS INNER JOIN PROGRAM ON STUDENTS.PID = PROGRAM.PID;

As for data reader, I've never used it. But, I assume you have to specify the source of the records. In that case you would use the SQL above as the source or create a Query or stored procedure to output the records...

thas123 · February 8th, 2006, 01:27 PM

Tell me how to specify these in the datareader.if anybody knows pls tell me.