Member Avatar for Korenai

good day, im practicing databases and .net, can you help me in relating multiple tables and manipulating a listview?

my scenario is about school subjects of a student, i have a database with data that has 2 tables, which is 'fnsh_table' and 'ofrd_subjects'

fnsh_table

Student_No nchar(4)
Subj_No nvarchar(10)
Title nvarchar(20)
Remarks nvarchar(6)-- either 'passed' or 'failed'

ofrd_subjects

EDPCode nchar(8)
Subj_No nvarchar(10)
Title nvarchar(20)
Time_begin time
Time_end time
Units nchar(1)
RmNo nchar(3)
Days nvarchar(3)
Prerequisites nvarchar(30)


heres a part of whats inside my tables.


fnsh_table

Student_No, Subj_No, Title, Remarks

3432 IT10 Programming 1 Passed
3432 IT11 Programming 2 Passed
3432 IT13 Programming 3 Passed
3432 Hist1 History 1 Failed

ofrd_subjects

EDPCode,Subj_No, Title, Time_Begin, Time_End, Units, RmNo Days, Prerequisites


51232134 IT10 Programming 12:30 PM 3:30 PM 3 219 MWF
45464323 IT11 Programming 2 10:30 AM 11:30 AM 3 218 MWF
99932311 Hist1 History 1 5:30 PM 6:30 PM 2 113 TTH
12736238 IT14 Multimedia 1 7:30 AM 8:30 AM 3 510 MWF Programming 1, Programing 2
29472631 IT15 Animation 9:30 AM 10:30AM 3 201 TTH Multimedia 1
92846341 IT18 COBOL 1:30 PM 2:30 PM 3 410 TTH Programming 1, Programming 2
72631923 IT20 File Org 9:30 AM 10:30AM 3 222 TTH COBOl, Programming 1, Programming 2
92345155 Soc 1 Social Sci 10:00 AM 12:00PM 3 101 MWF Hist1
31238912 Pol Sci Political Sci 12:45 PM 2:45 PM 3 333 SAT Soc 1

In my form, when i enter the student id number on a texbox, it will show to my listview1 what subjects he/she has already taken, and in my listview2 displays the offered subjects
can u please gve me an sql for my listview2 that filters out the subject already taken by the student...? pliz :(

And second is i want to display to my listview2 the only subjects that has prerequisites taken by the student with remarks
of 'passed', if not passed that subject will be offered again. what sql will i use? :(

im sorry i dont have sql sample yet, im confused on how to relate the tables...:(

Any advice is deeply apreciated.. THANK YOU :)

Edited by Korenai because: n/a
  • 2 Contributors
  • 6 Replies
  • 122 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by smantscheff
Member Avatar for smantscheff

Give it at least a try. Set up some SQL code which shows the unfiltered data. Then add the filter as a WHERE clause.
Relate the tables like this:

SELECT * from ofrd_subjects as subjects, fnsh_table as students WHERE students.Subj_No = subjects.Subj_No

Is it a prerequisite that you use unreadable abbreviations as table names?

Edited by TrustyTony because: fixed formating
Member Avatar for Korenai

thanks for your advice, ill give this a try..:)

Member Avatar for Korenai

Give it at least a try. Set up some SQL code which shows the unfiltered data. Then add the filter as a WHERE clause.
Relate the tables like this:
SELECT * from ofrd_subjects as subjects, fnsh_table as students WHERE students.Subj_No = subjects.Subj_No
Is it a prerequisite that you use unreadable abbreviations as table names?

i want to compare the prerequisite of a certain subject in the ofrd_table with the taken subject of a student in the finish_table and it must have a remarks of passed otherwise that subject will be offered again,,,

Edited by mike_2000_17 because: Fixed formatting
Member Avatar for smantscheff

You are confusing your table names. You should try it with more meaningful ones. Having "table" as table name is not especially helpful.
That said, try it with
SELECT * FROM ofrd_subjects as subjects, fnsh_table as students WHERE students.Subj_No = subjects.Subj_No and remarks = 'passed'

Member Avatar for Korenai

You are confusing your table names. You should try it with more meaningful ones. Having "table" as table name is not especially helpful.
That said, try it with
SELECT * FROM ofrd_subjects as subjects, fnsh_table as students WHERE students.Subj_No = subjects.Subj_No and remarks = 'passed'

thanks a lot, ill try ur code, hope it helps. thanks again, if you have more advice pls post it..

Member Avatar for smantscheff

Since you explicitly asked for more advice, here it is:
Write in English, not in amputated abbreviations.
Submit complete test cases with CREATE TABLE statements, INSERT data statements and the queries which are problematic. That way others can just paste and copy your test case into mysql and analyze the problem.
Do not abbreviate table names, field names or variable names. Make them meaningful.
When working with databases, solve as much as you can on the database level and only the rest with the display interface. Use the command line mysql to test and refine your solutions.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.