query to insert values into table

Question

2.00/5 (1 vote)

See more:

if a student enters his name in one textbox and selects many subjects he has from listbox and submits ,
what will be the design of table to insert student name and selected subjects, subjects vary according to student choice, and what will be the query for this ?

Posted 21-Oct-12 6:11am

Jasbeer Singh

Add a Solution

2 solutions

Solution 2

You need following tables;
Student - StudentId, Name - Master table for students
Subject - SubjectId, Name - Master table for subjects
StudentSubjects - Id, StudentId, SubjectId - Mapping table to know subjects mapped to a student

For retrieving all the subject information related to a student you have to join all the above 3 tables as below;

SQL

SELECT SB.STUDENTID,S.NAME AS STUDENTNAME,SJ.NAME AS SUBJECTNAME FROM STUDENTSUBJECTS SB INNER JOIN STUDENT S ON SB.STUDENTID=S.STUDENTID
INNER JOIN SUBJECT SJ ON SB.SUBJECTID=SJ.SUBJECTID WHERE SB.STUDENTID=1

Posted 21-Oct-12 7:11am

vv_shameer

Updated 21-Oct-12 11:30am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Accepted Answer · 2012-10-21T07:02:00

Well, this is not the kind of question we like here. You have to use what you have learned, or learn what you have to.
What you described here needs at least tree tables, thus can not be performed in one insert statement (not query!), but you can combine them. The minimalist design could be this one:
Students table:

SQL

CREATE TABLE [dbo].[students](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_students] PRIMARY KEY ([id] ASC)
)

Subjects table:

SQL

CREATE TABLE [dbo].[subjects](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[title] [varchar](50) NOT NULL,
 CONSTRAINT [PK_subjects] PRIMARY KEY (	[id] ASC )
)

Application table:

SQL

CREATE TABLE [dbo].[application](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[student_id] [int] NOT NULL,
	[subject_id] [int] NOT NULL,
 CONSTRAINT [PK_application] PRIMARY KEY ( [id] ASC ),
 CONSTRAINT [FK_application_students] FOREIGN KEY([student_id]) REFERENCES [dbo].[students] ([id]),
 CONSTRAINT [FK_application_subjects] FOREIGN KEY([subject_id]) REFERENCES [dbo].[subjects] ([id])
)

And now, let's add some subjects first:

SQL

insert into subjects(title) values('Math');
insert into subjects(title) values('Chimie');
insert into subjects(title) values('English');

And finally, let's add a student with it's applications in a single query:

SQL

insert into students(name) values('John');
DECLARE @newid int;
SET @newid = (SELECT IDENT_CURRENT('students'));
insert into application(student_id,subject_id) values(@newid, 1);//Math
insert into application(student_id,subject_id) values(@newid, 3);//English