mysql - 如何连接一个表中的多个字段并将结果与​​另一个表中的另一个字段进行比较？

Question

我有以下情况:

table 1 -> publications

table 2 -> users

在出版物的一些详细信息中的表一中，我有 20 个与作者相关的字段，确切地说是他们的姓名和姓氏(这些字段:n1、l1、n2、l2、n3、l3... 和等等)。

在表二中，我有一个字段包含用户的姓名。

我只想显示表 1 中的姓名和姓氏与表 2 中的姓名和姓氏相同的出版物。

这是我到目前为止的查询:

$sql ="SELECT *, concat_ws(' ',n1,l1), concat_ws(' ',n2,l2) AS my
       FROM #__publications WHERE my IN
       (SELECT name FROM #__users WHERE name = '".$l_user."')";

我知道我的想法可能是错误的。你可以帮帮我吗？如果您能给我一些建议，我将不胜感激。

Answer 1

您应该从“出版物”表中删除作者，并创建第三个表将用户与出版物链接起来。

PublicationAuthors

PublicationID  int
UserID int

然后您的查询只需检查该表即可查看用户是否与该发布相关联。另外，您可以根据需要拥有任意数量的作者，而无需向出版物添加新列，并且如果有人更改姓名，也不会破坏与出版物的关系。

这是一个示例(我为此使用了 SQL Server，因此可能存在细微的语法差异):

CREATE TABLE Publications(
    [PublicationID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](128) NOT NULL,
    [DatePublished] [DateTime]
PRIMARY KEY CLUSTERED 
(
    [PublicationID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE Authors(
    [AuthorID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](128) NOT NULL,
    [LastName] [nvarchar](128) NOT NULL
PRIMARY KEY CLUSTERED 
(
    [AuthorID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE PublicationAuthors(
    [PublicationID] [int],
    [AuthorID] [int]
PRIMARY KEY CLUSTERED
(
    [PublicationID] ASC,
    [AuthorID]
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


INSERT INTO Publications (Title, DatePublished) VALUES ('Sphere', '5/12/1987')
INSERT INTO Publications (Title, DatePublished) VALUES ('Jurassic Park', '11/1/1990') 
INSERT INTO Authors (FirstName, LastName) VALUES ('Michael', 'Chricton')
INSERT INTO Authors (FirstName, LastName) VALUES ('Andy', 'McKenna')
INSERT INTO PublicationAuthors (PublicationID, AuthorID) VALUES (1, 1)
INSERT INTO PublicationAuthors (PublicationID, AuthorID) VALUES (2, 1)
INSERT INTO PublicationAuthors (PublicationID, AuthorID) VALUES (2, 2)

--All Authors for this Publication
SELECT p.Title, p.DatePublished, a.FirstName, a.LastName
FROM Publications p
INNER JOIN PublicationAuthors pa
   ON pa.PublicationID = p.PublicationID
INNER JOIN Authors a
   ON a.AuthorID = pa.AuthorID
WHERE p.PublicationID = 2

--All Publications for this Author
SELECT p.Title, p.DatePublished, a.FirstName, a.LastName
FROM Authors a
INNER JOIN PublicationAuthors pa
   ON pa.AuthorID = a.AuthorID
INNER JOIN Publications p
   ON pa.PublicationID = p.PublicationID
WHERE a.AuthorID = 1

然后，当您意识到我拼错了作者的姓氏时，您可以只更新这一行，而无需触摸 Publications 表。