Inner Join or Full Join

Question

1.00/5 (1 vote)

See more:

Hi There,

I have 3 tables, aspnet_Users, aspnet_Genealogy and aspnet_PersonalTree. I am trying to have the data in aspnet_Genealogy to be assigned to a user. I beleive a full join of the three tables will accomplished this through a SP, aspnet_AddGenealogy. The code for the tables and SP are as follows:

Table, aspnet_Users:

SQL

CREATE TABLE [dbo].[aspnet_Users] (
    [UserId]           UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
    [UserName]         NVARCHAR (256)   NOT NULL,
    [LoweredUserName]  NVARCHAR (256)   NOT NULL,
    [MobileAlias]      NVARCHAR (16)    DEFAULT (NULL) NULL,
    [IsAnonymous]      BIT              DEFAULT ((0)) NOT NULL,
    [LastActivityDate] DATETIME         NOT NULL,
    PRIMARY KEY NONCLUSTERED ([UserId] ASC),

Table aspnet_Genealogy:

SQL

CREATE TABLE [dbo].[aspnet_Genealogy] (
    [GenealogyId]       INT            IDENTITY (1, 1) NOT NULL,
    [FamilyName]        CHAR (200)     NOT NULL,
    [FirstName]         CHAR (200)     NULL,
    [MiddleName1]       CHAR (200)     NULL,
    [MiddleName2]       CHAR (200)     NULL,
    [MiddleName3]       CHAR (200)     NULL,
    [DOB]               NVARCHAR (100) NOT NULL,
    [Gender]            CHAR (20)      NULL,
    [COOB]              CHAR (200)     NULL,
    [SOB]               CHAR (200)     NULL,
    [COB]               CHAR (200)     NULL,
    [Newsletter]        NVARCHAR (10)  NULL,
    [DateTimeGenealogy] DATETIME       NOT NULL,
    PRIMARY KEY CLUSTERED ([GenealogyId] ASC)
);

Table aspnet_PersonalTree:

SQL

CREATE TABLE [dbo].[aspnet_PersonalTree] (
    [UserId]         UNIQUEIDENTIFIER NOT NULL,
    [GenealogyId]    INT              NOT NULL,
    );

The SP, aspnet_AddGenealogy:

SQL

CREATE PROCEDURE [dbo].[aspnet_AddGenealogy]
    @FamilyName char(200),
    @MiddleName1    char(200),
    @MiddleName2    char(200),
    @MiddleName3    char(200),
    @FirstName  char(200),
    @DOB    Nvarchar(100),
    @Gender char(20),
    @COOB   char(200),
    @SOB    char(200),
    @COB    char(200),
    @Newsletter Nvarchar(10),
    @DateTimeGenealogy  DateTime
 
AS
Begin
    INSERT INTO aspnet_Genealogy(FamilyName, MiddleName1, MiddleName2, MiddleName3, FirstName, DOB, Gender, COOB, SOB, COB, Newsletter, DateTimeGenealogy) VALUES(@FamilyName, @MiddleName1, @MiddleName2, @MiddleName3, @FirstName, @DOB, @Gender, @COOB, @SOB, @COB, @Newsletter, @DateTimeGenealogy)
END

I have only done the insert which works, I need to known if a full join will accomplish assigning ownership of data between user and Genealogy table. I also need others to be able to assign the same data to other users, that is why I created a third table, aspnet_PersonalTree.

I have never did a Full join and can I add the full join to the SP, aspnet_AddGenealogy?

Can anyone help me?

Thanks

ASW

Posted 14-Nov-13 6:36am

Member 9142936

Add a Solution

Comments

SomeGuyThatIsMe 14-Nov-13 13:24pm

what are you planning to join on? The only fields that could be close based on your tables would be a combination of names, but since genealogy has several fields and user has just the 2 you will have a very hard and unreliable time matching them, assuming you want to match on name. If you just want to say genealogyId 1 is assigned to userId 3 pass the user id into your SP and do a second insert or make a SP_AssignToUser that does the insert into the personalTree table.

Member 9142936 15-Nov-13 11:29am

Thanks for your quick response, the planning stage is that I want to join aspnet_Users, and aspnet_PersonalTree together, and also join aspnet_Genealogy, and aspnet_PersonalTree together. The aspnet_Users is the user table and I would like to keep it that way, I also would like to keep the aspnet_Genealogy table is being use for other users to search and select to add from the aspnet_Genealogy table to be added to their family tree. One User can use the same record from the aspnet_Genealogy table as another user, but the record have a different relationship to the other user.

I have tried a inner join it did not work, I must be doing something wrong. I am trying to build a relationship between the tables aspnet_Users and aspnet_PersonalTree, the UserId. I am also trying to build a relationship between aspnet_Genealogy and aspnet_PersonalTree, GenealogyId. I have also created a INSERT as well for UserId and GenealogyId, but I am getting a ERROR, "error incorrect syntax near 'END' in sql statement". Do not know why I am getting this error. The code for the SP, is as follows:

CREATE PROCEDURE [dbo].[aspnet_AddGenealogy]
@FamilyName char(200),
@MiddleName1 char(200),
@MiddleName2 char(200),
@MiddleName3 char(200),
@FirstName char(200),
@DOB Nvarchar(100),
@Gender char(20),
@COOB char(200),
@SOB char(200),
@COB char(200),
@Newsletter Nvarchar(10),
@DateTimeGenealogy DateTime

AS
Begin
INSERT INTO aspnet_Genealogy(FamilyName, MiddleName1, MiddleName2, MiddleName3, FirstName, DOB, Gender, COOB, SOB, COB, Newsletter, DateTimeGenealogy) VALUES(@FamilyName, @MiddleName1, @MiddleName2, @MiddleName3, @FirstName, @DOB, @Gender, @COOB, @SOB, @COB, @Newsletter, @DateTimeGenealogy)

SELECT UserId
FROM aspnet_Users INNER JOIN aspnet_PersonalTree ON
aspnet_Users.UserId = aspnet_PersonalTree.UserId;

INSERT INTO aspnet_PersonalTree(UserId)

SELECT GenealogyId
FROM aspnet_Genealogy INNER JOIN aspnet_PersonalTree ON
aspnet_aspnet_Genealogy.GenealogyId = aspnet_PersonalTree.GenealogyId;

INSERT INTO aspnet_PersonalTree(GenealogyId)

END

Can you have a look to see what causing the error?

Thanks again for your help and look forward hearing from you soon.

Thanks

ASW

SomeGuyThatIsMe 15-Nov-13 12:06pm

beacuse your last insert is incomplete, it has no values clause or select statement after it.

Member 9142936 15-Nov-13 12:35pm

Thanks again I am able to complete the SQL, but I am now getting a error when I run the SP. The error is as follows:

"Server Error in '/' Application.
Cannot insert explicit value for identity column in table 'aspnet_PersonalTree' when IDENTITY_INSERT is set to OFF.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'aspnet_PersonalTree' when IDENTITY_INSERT is set to OFF.

Source Error:

Line 286: {
Line 287:
Line 288: throw ex;
Line 289:
Line 290: }

Source File: c:\Demo2\MemberShip and RoleProvider Demo\MemberShip and RoleProvider Demo\User\User.aspx.cs Line: 288

Stack Trace:

[SqlException (0x80131904): Cannot insert explicit value for identity column in table 'aspnet_PersonalTree' when IDENTITY_INSERT is set to OFF.]
User_User.Button1_Click(Object sender, EventArgs e) in c:\Demo2\MemberShip and RoleProvider Demo\MemberShip and RoleProvider Demo\User\User.aspx.cs:288
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9552874
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +103
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724"

I have checked the table, aspnet_PersonalTree and can figure out why the INSERT is not working. I have changed the SP as follows:

"CREATE PROCEDURE [dbo].[aspnet_AddGenealogy]
@FamilyName char(200),
@MiddleName1 char(200),
@MiddleName2 char(200),
@MiddleName3 char(200),
@FirstName char(200),
@DOB Nvarchar(100),
@Gender char(20),
@COOB char(200),
@SOB char(200),
@COB char(200),
@Newsletter Nvarchar(10),
@DateTimeGenealogy DateTime

AS
Begin
INSERT INTO aspnet_Genealogy(FamilyName, MiddleName1, MiddleName2, MiddleName3, FirstName, DOB, Gender, COOB, SOB, COB, Newsletter, DateTimeGenealogy) VALUES(@FamilyName, @MiddleName1, @MiddleName2, @MiddleName3, @FirstName, @DOB, @Gender, @COOB, @SOB, @COB, @Newsletter, @DateTimeGenealogy)

SELECT *
FROM aspnet_Users INNER JOIN aspnet_PersonalTree ON
aspnet_Users.UserId = aspnet_PersonalTree.UserId
INNER JOIN aspnet_Genealogy ON aspnet_PersonalTree.GenealogyId = aspnet_Genealogy.GenealogyId;

Declare @UserId UNIQUEIDENTIFIER, @GenealogyId Int

INSERT INTO aspnet_PersonalTree(UserId, GenealogyId) Values(@UserId, @GenealogyId)

END
"
I have DECLARED the Values in the SP, that allowed me to create the SP. Now I am having a Insert problem.

Can you help?

Thanks

ASW

SomeGuyThatIsMe 15-Nov-13 13:01pm

you dont assign @genealogyId anywhere, your select will also fail beacuse there is no link via the personalTree table yet. You need a way to get the genealogy id you just added for your insert w/out using a max. I never use ID columns i use a counter table and a sp to get the next value for whatever incremental id I want. that way i can call the SP and assign its return val to a varible in my main procedure and use it as many times as i want. i.e.

@nextId = usp_getNextId
insert into table(id..) values(@nextid...)

insert into joinTable(id, otherid) values(@nextId, @otherId)

hope that makes sense.

Member 9142936 15-Nov-13 13:16pm

I quite do not understand, is it supposed to go this way. Change the GenealogyId to ID, and Values to @NextId, as follows:

CREATE PROCEDURE [dbo].[aspnet_AddGenealogy]
@FamilyName char(200),
@MiddleName1 char(200),
@MiddleName2 char(200),
@MiddleName3 char(200),
@FirstName char(200),
@DOB Nvarchar(100),
@Gender char(20),
@COOB char(200),
@SOB char(200),
@COB char(200),
@Newsletter Nvarchar(10),
@DateTimeGenealogy DateTime

AS
Begin
INSERT INTO aspnet_Genealogy(FamilyName, MiddleName1, MiddleName2, MiddleName3, FirstName, DOB, Gender, COOB, SOB, COB, Newsletter, DateTimeGenealogy) VALUES(@FamilyName, @MiddleName1, @MiddleName2, @MiddleName3, @FirstName, @DOB, @Gender, @COOB, @SOB, @COB, @Newsletter, @DateTimeGenealogy)

SELECT *
FROM aspnet_Users INNER JOIN aspnet_PersonalTree ON
aspnet_Users.UserId = aspnet_PersonalTree.UserId
INNER JOIN aspnet_Genealogy ON aspnet_PersonalTree.Id = aspnet_Genealogy.Id;

@nextId = usp_getNextId insert into aspnet_PersonalTree(Id, UserId) values(@nextid, @UserId) insert into joinTable(Id, UserId) values(@nextId, @UserId)

insert into joinaspnet_PersonalTree(Id, UserId) values(@nextId, @otherId)

END

Is this how the SP work look?

Thanks again for your help, and look forward to hearing from soon...

Thanks

ASW

SomeGuyThatIsMe 15-Nov-13 13:28pm

CREATE PROCEDURE [dbo].[aspnet_AddGenealogy]
@FamilyName char(200),
@MiddleName1 char(200),
@MiddleName2 char(200),
@MiddleName3 char(200),
@FirstName char(200),
@DOB Nvarchar(100),
@Gender char(20),
@COOB char(200),
@SOB char(200),
@COB char(200),
@Newsletter Nvarchar(10),
@DateTimeGenealogy DateTime

AS
Begin
@nextId = usp_getNextId

INSERT INTO aspnet_Genealogy(GenealogyId,FamilyName, MiddleName1, MiddleName2, MiddleName3, FirstName, DOB, Gender, COOB, SOB, COB, Newsletter, DateTimeGenealogy) VALUES(@nextId, @FamilyName, @MiddleName1, @MiddleName2, @MiddleName3, @FirstName, @DOB, @Gender, @COOB, @SOB, @COB, @Newsletter, @DateTimeGenealogy)
--possibly do not need?
SELECT *
FROM aspnet_Users INNER JOIN aspnet_PersonalTree ON
aspnet_Users.UserId = aspnet_PersonalTree.UserId
INNER JOIN aspnet_Genealogy ON aspnet_PersonalTree.Id = aspnet_Genealogy.Id;

insert into aspnet_PersonalTree(GenealogyId, UserId) values(@nextid, @UserId)

END

I've modified your proc some, this text box is rather small so sorry if i added some typo's or missed something. I added the GenealogyId column to your first insert and the @nextId to be inserted as its value which is then used for the insert into personalTree with the userId. The catch is that you'll have to change genealogy to not use an identity column and create the getNextId procedure and a table to hold the values it deals with. The concept is basicly implementing your own version of an Identity column using a SP and table rather than the build in db functionality. Unless there is a way to get/reserve the next Id for the table so you can use it in multiple inserts.

Member 9142936 15-Nov-13 13:52pm

Does any of my Tables need change, there are two table that have GenealogyId in them. The Value nextId the SP is not accepting.

Could this be the Tables.

Thanks again for your quick response.

Thanks

ASW

SomeGuyThatIsMe 15-Nov-13 15:07pm

I dont think you can insert a value into an identity column, you might have to change it to an integer. since the sp getnextId is replicating the functionality of an identity column. Its also possible that the sql i posted has a typo or two in it since i modified it in this reply box.

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)

Maciej Los · Accepted Answer · 2013-11-14T10:21:00

Solution 1

Please, see this article: Visual Representation of SQL Joins[^]. I hope it will help you to find an answer ;)

Posted 14-Nov-13 10:21am

Maciej Los

Updated 14-Nov-13 10:22am

v2

Comments

CHill60 14-Nov-13 16:26pm

Oh I like that link! Bookmarked. My 5!

Maciej Los 14-Nov-13 16:31pm

;)

Raja Sekhar S 14-Nov-13 23:00pm

As usual nice one Maciej Los.... +5!

Maciej Los 16-Nov-13 11:36am

Thank you ;)