SQL Server multiple choice in table field to parent table

Question

I have two tables in one SQL-database. A parent table calles dbo.tab_authors and a child table called dbo.tab_articles.

The tables are defined in that way:

dbo.tab_authors:
    (PK) ID                 int
         Name               string

dbo.tab_articles:
    (PK) ID                 int
         Title              string
    (FK) Author_first       int
    (FK) Authors_further    int

Within the dbo.tab_articles i want to select the corresponding authors from dbo.tab_authors. Author_first (the first author of an arcticle) and Authors_further (all the remaining authors) have foreign keys (FK) to primary key (FK) ID of dbo.tab_authors. But in Author_first there is a 1:1 relation and in Authors_furhter I want to install a 1:n relation to the parent table dbo.tab_authors.

Lets assume following datasets as an example

dbo.tab_authors:
    ID            Name
    1             Author the First
    2             Author the Second
    3             Author the Third

dbo.tab_articles
    ID   Title                     Author_first             Authors_further
    1    Important Article         1                        2;3
    2    More Important Article    2                        1;3
    3    Most Important Article    3                        1;2

The question:

How to define the relationship between dbo.tab_articles.Authors_further and dbo.tab_authors.ID in SQL? (I am using MS SQL Server Managment Studio 18.8)

I would appreciate any advice!

You need a "bridge" table to define your many to many relationship. — Thom A, Apr 01 '21 at 10:59
Thanks for this very fast answer! The keyword than is a "bridge table" like this question: https://stackoverflow.com/questions/32363658/how-to-solve-this-use-a-bridge-table :-) — Aroc, Apr 01 '21 at 11:04

Thom A · Accepted Answer · 2021-04-01T11:14:58.427

As I mentioned in the comments, you would need a bridge table here, to manage the many to many relationship. This means your design would look something like this:

CREATE TABLE dbo.Author (ID int IDENTITY NOT NULL,
                         [Name] nvarchar(100));
GO
ALTER TABLE dbo.Author ADD CONSTRAINT PK_Author PRIMARY KEY (ID);
GO
CREATE TABLE dbo.Article (ID int IDENTITY NOT NULL,
                          Title nvarchar(50),
                          PrimaryAuthor int);
GO
ALTER TABLE dbo.Article ADD CONSTRAINT PK_Article PRIMARY KEY (ID);
ALTER TABLE dbo.Article ADD CONSTRAINT FK_ArticleAuthor FOREIGN KEY (PrimaryAuthor) REFERENCES dbo.Author (ID);
GO
CREATE TABLE dbo.ArticleSecondaryAuthor (AuthorID int NOT NULL,
                                         ArticleID int NOT NULL);
GO
ALTER TABLE dbo.ArticleSecondaryAuthor ADD CONSTRAINT PK_ArticleSecondaryAuthor PRIMARY KEY (AuthorID,ArticleID);
ALTER TABLE dbo.ArticleSecondaryAuthor ADD CONSTRAINT FK_ArticleSecondaryAuthor_Author FOREIGN KEY (AuthorID) REFERENCES dbo.Author (ID);
ALTER TABLE dbo.ArticleSecondaryAuthor ADD CONSTRAINT FK_ArticleSecondaryAuthor_Article FOREIGN KEY (ArticleID) REFERENCES dbo.Article (ID);

Then your data would likely look something like this:

INSERT INTO dbo.Author ([Name])
VALUES('John Smith'),('Jane Bloggs'),('Ashley Brown');
GO
INSERT INTO dbo.Article (Title,PrimaryAuthor)
VALUES('How to build a Boat',2),
      ('Relaxing to blues',1),
      ('How I lost my hair and back',1);
GO

INSERT INTO dbo.ArticleSecondaryAuthor (AuthorID,ArticleID)
VALUES(2,2),
      (3,2),
      (3,3);

And just to show how you would get the data, you could do so along the lines of one of the following:

--Multiple Joins to Author
SELECT A.Title,
       AA.[Name],
       STRING_AGG(SA.Name,', ') WITHIN GROUP (ORDER BY SA.ID) AS SecondaryAuthors
FROM dbo.Article A
     JOIN dbo.Author AA ON A.PrimaryAuthor = AA.ID
     LEFT JOIN dbo.ArticleSecondaryAuthor ASA ON A.ID = ASA.ArticleID
     LEFT JOIN dbo.Author SA ON ASA.AuthorID = SA.ID
GROUP BY A.ID, --Just in case of articles with the same name
         A.Title,
         AA.Name;
GO
--Conditional Aggregation
SELECT A.Title,
       MAX(CASE A.PrimaryAuthor WHEN AA.ID THEN AA.Name END),
       STRING_AGG(CASE A.PrimaryAuthor WHEN AA.ID THEN NULL ELSE AA.Name END,', ') WITHIN GROUP (ORDER BY AA.ID) AS SecondaryAuthors
FROM dbo.Article A
     LEFT JOIN dbo.ArticleSecondaryAuthor ASA ON A.ID = ASA.ArticleID
     JOIN dbo.Author AA ON AA.ID IN (A.PrimaryAuthor,ASA.AuthorID)
GROUP BY A.ID, --Just in case of articles with the same name
         A.Title,
         A.PrimaryAuthor;

db<>fiddle

SQL Server multiple choice in table field to parent table

1 Answers1