内部加入或完全加入 [英] Inner Join or Full Join

问题描述

你好，



我有3个表，aspnet_Users，aspnet_Genealogy和aspnet_PersonalTree。我试图将aspnet_Genealogy中的数据分配给用户。我相信这三个表的完全连接将通过SP，aspnet_AddGenealogy完成。表格和SP的代码如下：



表格，aspnet_Users：

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:

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),

表aspnet_Genealogy：

Table aspnet_Genealogy:

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)
);

表aspnet_PersonalTree：

Table aspnet_PersonalTree:

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

SP，aspnet_AddGenealogy：

The SP, aspnet_AddGenealogy:

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

我只完成了有效的插入，我需要知道完全连接是否会完成在用户和Genealogy表之间分配数据的所有权。我还需要其他人能够将相同的数据分配给其他用户，这就是为什么我创建了第三个表，aspnet_PersonalTree。



我从来没有做过全连接我可以将完整的连接添加到SP，aspnet_AddGenealogy吗？



任何人都可以帮助我吗？







谢谢







ASW

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

推荐答案

请参阅此文章： SQL连接的可视化表示 [ ^ ]。我希望它能帮助你找到答案;）

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

这篇关于内部加入或完全加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！