INSERT into SQL Server Tables Using JOIN and Foreign Keys

Question

I am trying to create a Students table, a Students_Images table and a Photo_Collection table. Image paths are stored in Student_Images, each image is linked to a Photo_Collection row, and each Photo_Collection is related to a Student row.

The Student records will already exist. Then a student can create a Photo Collection for themselves, and add photos to this Photo Collection. So each image will need to be associated with a Photo Collection, and each Photo Collection associated with a Student.

I was told to use the below code:

create table dbo.Students
(
  ID
    int not null identity( 1, 1 )
    constraint [Students.ID.PrimaryKey]
      primary key clustered,

  Name
    nvarchar( 50 )
)

create table dbo.Student_Images
(
  ID
    int not null identity( 1, 1 )
    constraint [Student_Images.ID.PrimaryKey]
      primary key clustered,

  Student_ID
    int not null
    constraint [Student_Images.to.Student]
      foreign key references dbo.Students( ID )

  Filename
    nvarchar( 250 ) null,

  Description
    nvarchar( 250 ) null
)

create table dbo.Photo_Collection
(
  ID
    int not null identity( 1, 1 )
    constraint [Photo_Collection.ID.PrimaryKey]
      primary key clustered,

  Name
    nvarchar( 250 ) null
)

create table dbo.Photo_Collection_Images
(
  Photo_Collection_ID
    int not null
    constraint [Photo_Collection_Images.to.Photo_Collection]
      foreign key references dbo.Photo_Collection( ID ),

  Student_Image_ID
    int not null
    constraint [Photo_Collection_Images.to.Student_Images]
      foreign key references dbo.Student_Images( ID )
)

Below is an example of how these tables would look when populated:

I can INSERT into Students, Student_Images and Photo_Collection using the following SQL:

protected void btnAddImage_Click(object sender, EventArgs e)
{

    addImage();
}

public void addStudent()
{
    string cmdText = "INSERT INTO Students (Name) VALUES (@N)";

    //====== Providning information to SQL command object about which query to 
    //====== execute and from where to get database connection information.
    SqlCommand cmd = new SqlCommand(cmdText, con);

    //===== Adding parameters/Values.
    cmd.Parameters.AddWithValue("@N", txtStudentName.Text.ToString());
    //===== To check current state of the connection object. If it is closed open the connection
    //===== to execute the insert query.
    if (con.State == ConnectionState.Closed)
    {
        con.Open();
    }

    //===== Execute Query.
    cmd.ExecuteNonQuery();

    //===== close the connection.
    con.Close();
}

public void addCollection()
{
    string cmdText = "INSERT INTO Photo_Collection (Name) VALUES (@N)";

    //====== Providning information to SQL command object about which query to 
    //====== execute and from where to get database connection information.
    SqlCommand cmd = new SqlCommand(cmdText, con);

    //===== Adding parameters/Values.
    cmd.Parameters.AddWithValue("@N", txtPhotoCollectionName.Text.ToString());
    //===== To check current state of the connection object. If it is closed open the connection
    //===== to execute the insert query.
    if (con.State == ConnectionState.Closed)
    {
        con.Open();
    }

    //===== Execute Query.
    cmd.ExecuteNonQuery();

    //===== close the connection.
    con.Close();
}

public void addImage()
{
    string cmdText = "INSERT INTO Student_Images (Student_Id, Filename, Description) VALUES (@S, @F, @D)";

    //====== Providning information to SQL command object about which query to 
    //====== execute and from where to get database connection information.
    SqlCommand cmd = new SqlCommand(cmdText, con);

    //===== Adding parameters/Values.
    cmd.Parameters.AddWithValue("@S", ddlStudentNames.SelectedValue);
    cmd.Parameters.AddWithValue("@F", fuImage.FileName.ToString());
    cmd.Parameters.AddWithValue("@D", txtImageDescription.Text.ToString());


    //===== To check current state of the connection object. If it is closed open the connection
    //===== to execute the insert query.
    if (con.State == ConnectionState.Closed)
    {
        con.Open();
    }

    //===== Execute Query.
    cmd.ExecuteNonQuery();

    //===== close the connection.
    con.Close();
}
protected void btnAddStudentAndCollection_Click(object sender, EventArgs e)
{
    addStudent();
    addCollection();
}

}

I need to retrieve from the database, something like this:

But I don't know how to INSERT and SELECT using the Photo_Collection_Images table because I don't know how to reference the foreign key's I need in order to link the tables together.

Can anyone tell me how to successfully create a photo collection and add an image to it?

The reason I am finding this difficult is because I have to join a number of tables.

Answer 1

To insert images into a collection without knowing the ids yet.

/* insert without knowing the ids yet */

insert into Photo_Collection_Images (Photo_Collection_Id , Student_Image_Id)
select pc.Id, si.Id
from dbo.photo_collection as pc 
  inner join dbo.student_images as si on pc.student_id = si.student_id
  inner join dbo.students as s on s.id=si.student_id
where s.Name = @S -- student_name
  and pc.Name = @PC -- photo_collection.name
  and si.FileName = @F -- student_images.filename

To get all collections for a student:

/* all collections for one student*/
select pc.Name, si.Id, si.filename, si.description
from dbo.photo_collection as pc
inner join dbo.photo_collection_images pci on pc.id = pci.photo_collection_id
inner join dbo.student_images si on pci.student_image_id = si.id
where si.student_id = @s
-- and pc.name = @N /* one collection */

And about your question concerning table order, this is the same as the previous query:

/* all collections for one student*/
select pc.Name, si.Id, si.filename, si.description
from dbo.student_images as si 
inner join dbo.photo_collection_images pci on pci.student_image_id = si.id
inner join dbo.photo_collection as pc on pc.id = pci.photo_collection_id
where si.student_id = @s
-- and pc.name = @N /* one collection */

To get all collections for a student by name:

/* all collections for one student*/
select pc.Name, si.Id, si.filename, si.description
from dbo.student as s 
inner join dbo.student_images as si on s.id = si.student_id
inner join dbo.photo_collection_images pci on pci.student_image_id = si.id
inner join dbo.photo_collection as pc on pc.id = pci.photo_collection_id
where s.Name = @s
-- and pc.name = @N /* one collection */

To get all collections for all students

/* all collections for all students */
select student_name = s.name, pc.Name, si.Id, si.filename, si.description
from dbo.student as s 
inner join dbo.student_images as si on s.id = si.student_id
inner join dbo.photo_collection_images pci on pci.student_image_id = si.id 
inner join dbo.photo_collection as pc on pc.id = pci.photo_collection_id

Answer 2

Not sure if I understood you right, but consider the following query. It gives you for every student the list of his images and the collections in which they occur. When ordering the result by student name and photo collection name, then your code that iterates over the result would just have to watch whenever photo collection name or student name changes in order to start a new "group" in the interface.

Hope it helps.

select s.*, pc.name, si.filename, si.description  from student s
  left outer join student_images si on s.id = si.student_id
  left outer join photo_collection_images pci on pci.student_image_id = si.id
  left outer join photo_collection pc on pci.photo_collection_id = pc.id
order by s.name, pc.name, si.id