hi,

I have a doubt in writing sql statement.nested subquery infact.
i want to generate a timetable.
im inserting values in timetable table which enters in the following format:

select PID - ie program ID(in dropdownlist)
enter timetable ID - TTID (in textbox)
Day P1 P2 .... P7 PID
sat bmr301(textbox) cis306 fff BBA
sun bmr301(textbox) cis306 bba

it goes into timetable table which contains the following format
ttid day p1 p2..... p7 pid
1 sat bmr301 cis306 1
1 sun bmr301 cis306 1

now i want to display a timrteable when a student is clicking the timetable link.
so im prompting the student to enter student id ie SID
enter SID (IN TEXTBIOX)

AFTER THAT IT CHECKS IN THE COURSESTAKEN TABLE WHICH ALL SUBJECTS TAKEN by tht particular student id say 010302050.

the coursestaken table is the following format

coursestaken table
cid cname sid
bmr301 mobile computing 010302050
cis306 marketing 010302050

sudent has already registered for a program ID ie PID - example say CIS(computers) or BBA (business).after the student enters thd sid,
it checks in the coursestaken table what all subjects taken by sid 010302050

ie select cid from coursestaken where sid='010302050'
it dispalys a list of courses where sid 01030250 has regstered for.
so one by one it shud take the first record cid bmr301 and checks in the timetable table to match whetehr in p1 or p2 ....till p7, bmr301 exists in the day SAT or sun for the particular PID and for the next record and so on.this is done to generate timetable according to students registerd courses.

so i want the nested sql statement for this.can anybody help mne out.
i made an sql statement .i am not sure this is the rite one.

select * from timetable where pid='1' and p1=(select cid from coursetaken where sid='010302050')

plese tel me a correct one.im not sure abt this.

Try:

SELECT * FROM timetable
WHERE pid = 1' and p1 in (SELECT cid from coursetaken WHERE sid = '010302050')

I think that syntax might work but I'm not sure if its going to give you the results you require.

i know the synatx might work.but how to pass each time the cid value in the coursestaken to the timetable to check. i am badly looking out for a solution for this.somebody knows pls reply me back.

The query like
   SELECT * FROM timetable
   WHERE pid = 1 and p1 in (SELECT cid from coursetaken WHERE
   sid = '010302050')
will give you the entire rows that have the courseid (cid) you get from COURSETAKEN table.

But if I am not mistaken you want the column names in which the course exists.
In this case you will have to use 'sysobjects' and 'sysindexes' tables. These tables contain the details of the tables and the columns respectively.

Regards

I'm having trouble understanding exactly what you're trying to do. And I don't know the table structure.

Can you post the CREATE TABLE code for the tables involved?

Also, is this related to any sort of application? If this is related to an application it would be a lot easier. You could grab the sid and then loop through the timetable TABLE and check to see if that cid exists in the coursetaken TABLE WHERE sid = '00302050'. If you get a match then you can display that value and so on.

If the tables have primary and foreign keys all you might need to do is a simple INNER JOIN. So all that would be present in the coursetaken table is the sid and cid columns. If you already know all of this and I just don't understand the situation, please excuse me.

SELECT * from timetable INNER JOIN coursetaken ON timetable.cid = coursetaken.cid WHERE coursetaken.sid = '010302050'