1

i have two tables, Subjects and Departments, with a many to many relationship. The junction table is called Departments_Subjects_junction. i use sql server dbms.

Subjects:
subjects_id(primary key)
subject_name


Departments:
department_id(primary key)
department_name

Departments_Subjects_junction:
subject_id(primary key)
department_id(primary key)

I am able to store data in all the three tables.

Now i want to display the records for the user to see. For eg, the user must see the list of all subjects for each department. Something like this:

DepartmentA- Subject1, subject2, subject3, Subject4
DepartmentB- subject2, subject3, subject5
DeparmtentC- subject1, subject3, subject6.

How can i implement this in the best manner?using datagrid or listview or somethingelse? Please help. Thanks.

chris_techno25
  • 2,401
  • 5
  • 20
  • 32
user2444712
  • 65
  • 1
  • 13
  • I haven't been doing SQL for a while, but I remember there is `FOR XML PATH` statement, which allows you to de-normalize results on the fly into a string, like you want in the above. Check this out: http://stackoverflow.com/questions/6754889/for-xml-path-and-string-concatenation – Victor Zakharov Mar 17 '14 at 14:02

1 Answers1

0

It doesn't matter what kind of control you use to display data, whether it's a datagrid or listview, both work fine and it all depends on how you want them to look. So I'm taking the liberty to say that your question really is about querying data against the different tables and showing it on a list. This is purely SQL.

Your table Departments_Subjects_junction cannot have both fields under it as primary keys since the way I see it, it's the table that defines what subjects a certain department has. So it should be...

Departments_Subjects_junction
department_id(primary key)
subject_id

Then you can query using this SQL statement...

SELECT T2.department_name, T3.subject_name
FROM Departments_Subjects_junction T1
INNER JOIN Departments T2
ON T1.department_id=T2.department_id
INNER JOIN Subjects T3
ON T1.subject_id=T3.subject_id;

Okay here's what you'll have to do...

1) Add a ListView control to your form.

2) Add a single column during design time.

3) Set the ListView's View Property to Details.

4) Add this code anywhere and just call it.

Private Sub displaydata()
    Dim item As ListViewItem
    Dim dt As New DataTable

    dt.Clear()
    ListView1.Groups.Clear()
    da = New SqlDataAdapter("SELECT department_name FROM Departments", con) 
    da.Fill(dt)
    For x = 0 To dt.Rows.Count - 1
        ListView1.Groups.Add(dt.Rows(x).Item("department_name"), dt.Rows(x).Item("department_name"))
    Next x

    dt.Clear()
    ListView1.Items.Clear()
    da = New SqlDataAdapter("SELECT T2.department_name, T3.subject_name FROM Departments_Subjects_junction T1 INNER JOIN Departments T2 ON T1.department_id=T2.department_id INNER JOIN Subjects T3 ON T1.subject_id=T3.subject_id ", con) 
    da.Fill(dt)
    For x = 0 To dt.Rows.Count - 1
        item = New ListViewItem
        item.Text = dt.Rows(x).Item("subject_name")
        item.Group = ListView1.Groups(dt.Rows(x).Item("department_name"))
        ListView1.Items.Add(item)
    Next x
    dt.Clear()
End Sub

This should work.

chris_techno25
  • 2,401
  • 5
  • 20
  • 32
  • thanks. its a many to many relationship. a department has several subjects .At the same time some subjects are common to all departments. Can you please change the query accordingly? – user2444712 Mar 17 '14 at 06:25
  • I see, try the SQL statement first. It works the same :) – chris_techno25 Mar 17 '14 at 06:27
  • it does work but not in the way i want it to. in the output, the department names get repeated, which i dont want. the department name must be shown only once, like i have posted in my question. – user2444712 Mar 17 '14 at 06:39
  • Can you post a screenshot of your actual result? And your desired output? It'll be much easier to solve your problem if I saw your current actual result. – chris_techno25 Mar 17 '14 at 06:47
  • So would you like to display all the department_name? Or you select the department_name by choice and the subjects come out? – chris_techno25 Mar 17 '14 at 07:37
  • when i use GROUP BY, i dont get any output. – user2444712 Mar 17 '14 at 07:37
  • what i wish to see is exactly how i have given in the question
     – user2444712 Mar 17 '14 at 07:38
  • DepartmentA- Subject1, subject2, subject3, Subject4 DepartmentB- subject2, subject3, subject5 DeparmtentC- subject1, subject3, subject6. – user2444712 Mar 17 '14 at 07:39
  • Okay, I get it now. Can you post your code on the whole querying thing? Like are you using a datatable or a dataset? I need to go out now and I'll be back later with your answer. Just post your code and when I get back, I'll edit the code to give you want you want. – chris_techno25 Mar 17 '14 at 07:43
  • By the way, my SQL statement is correct, it's just the way you displayed your data that's incorrect. SQL returned rows' nature cannot be altered, but by tweaking the displaying code part, you should get what you want. You can use groupings property of your control to achieve the desired output. If you can't get it, I'll answer your question when I get back. – chris_techno25 Mar 17 '14 at 07:46
  • Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load da.Dispose() ds.Clear() da = New SqlDataAdapter("SELECT T2.department_name, T3.subject_name FROM Departments_Subjects_junction T1 INNER JOIN Departments T2 ON T1.department_id=T2.department_id INNER JOIN Subjects T3 ON T1.subject_id=T3.subject_id ", con) da.Fill(dt) dgStudents.DataSource = dt End Sub – user2444712 Mar 17 '14 at 07:54
  • I edited the code. I haven't tested it but it should work. Anyways, I don't know why you're clearing ds, like ds.clear when you're using dt as datatable. ds shouldn't be needed. If this answered your question, please mark it as answered, thanks. – chris_techno25 Mar 17 '14 at 13:05
  • Error: Expression does not produce a value "item.Group = ListView1.Groups(dt.Rows(x).Item("department_name"))" – user2444712 Mar 17 '14 at 15:58