SQL Insert into One-to-Many relationship

Question

0.00/5 (No votes)

See more:

HI
I have Table GymMember GymMemberID is PK

VB

GymMemberID(PK)
Name
Surname
Password
TrainingProgramID
Email
BMI

TABLE Log WHERE Log# is PK

VB

Log#(PK)
Weight
height
Chest
Forearms
Arm
Sholulders
Thighs
Calves
date
BodyMassIndex
BeforePicturePath
AfterPicturePath

Then linking Table GymMemberLog

VB

GymMemberID(PK)
Log#(PK)

I want to insert many logs per GymMember.
A gymMember can have many logs but a log can only be for one gym member.

Id like advice on how to insert eg Each gymMembers Log#'s should start at 1 and increment.

Posted 7-Oct-11 7:57am

SuperTeagz

Add a Solution

1 solution

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)

FastEvo8 · Answer 1 · 2011-10-07T08:18:00

Solution 1

You can use IDENTITY (1,1) for the Log# so you do not to increment it every time you add a new Log.
Also you need to add GymMemberID and a Foreign Key constraint to Log Table.
This way when you insert as new Log you will need to have the GymMemberID and you will be able to retrieve the new Log#
ie:

SQL

declare @LogNum int
INSERT INTO Log
VALUES (@height,@Chest,@Forearms,@Arm,@Sholulders,@Thighs,@Calves,@date,@BodyMassIndex,@BeforePicturePath,@AfterPicturePath,@GymMemberID)
SET @LogNum = SCOPE_IDENTITY()

Posted 7-Oct-11 8:18am

FastEvo8

Comments

SuperTeagz 7-Oct-11 14:31pm

Ok So I don't need the linking table(gymMemberLog)?

FastEvo8 7-Oct-11 14:39pm

This way you do not need the linking table.
If you had a many to may relationship than you would need it.
Make sure that you add the field GymMemberId in the log table and add the FK constraints ( last step is optional,but it is recommended otherwise you need to manage relationship integrity)

SuperTeagz 7-Oct-11 15:37pm

Then problem i have is that I want each member to have a logs 1to watever
eg Justin has logNumber1,LogNumber2,LogNumber3
and then Haris has logNumber1,LogNumber2,LogNumber3 as well.
but what is happening no is Justin has LogNumber1, Lognumber2,and then Haris Starts at LogNumber3.

FastEvo8 7-Oct-11 16:07pm

In that case when you read the data from the table you should use the RANK() function:
SELECT g.GymMemeberID,
RANK() OVER (PARTITION BY g.GymMemeberID ORDER BY l.Log#) AS LogNumber, *
FROM GymMember g
INNER JOIN Log l
ON g.GymMemberID=l.GymMemberID