Как объединить несколько полей из одной таблицы и сравнить результат с другим полем из другой таблицы?

Question

У меня следующая ситуация:

table 1 -> publications

table 2 -> users

В таблице 1 среди некоторых деталей публикаций у меня есть 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

Вы должны удалить авторов из таблицы Publications и создать третью таблицу, которая связывает пользователей с публикациями.

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

Затем, когда вы поймете, что я неправильно написал фамилию автора, вы можете просто обновить эту строку, не касаясь таблицы публикаций.

Answer 2

Возможно, вам будет проще настроить вашу пользовательскую таблицу так, чтобы она содержала поле user_id в качестве первичного ключа и использовала его в качестве индекса в вашей таблице публикации.

CREATE TABLE __users
(
    user_id int(11) unsigned not null auto_increment,
    name varchar(32) not null default '',
    .... snip ....
    PRIMARY KEY (user_id)
);

CREATE TABLE __publications
(
    publication_id int(11) unsigned not null auto_increment, 
    user_id1 int(11) unsigned not null default 0,
    user_id2 int(11) unsigned not null default 0,
    n1 varchar(32) not null default '',
    l1 varchar(32) not null default '',
    n2 varchar(32) not null default '',
    l2 varchar(32) not null default '',
    .... snip ....
    PRIMARY KEY (publication_id),
    INDEX user_id1 (user_id1),
    INDEX user_id2 (user_id2)
);

SELECT p.*,  concat_ws(' ', p.n1, p.l1) AS author1,  concat_ws(' ', p.n2, p.l2) AS author2
FROM __users AS u
JOIN __publications AS p ON (u.user_id = p.user_id1 OR u.user_id = p.user_id2)
WHERE u.name = $1_user
GROUP BY p.publication_id

Или вы можете просто выполнить соединение следующим образом:

SELECT p.*,  concat_ws(' ', p.n1, p.l1) AS author1,  concat_ws(' ', p.n2, p.l2) AS author2
FROM __users AS u
JOIN __publications AS p ON (u.name = concat_ws(' ', p.n1, p.l1) OR u.name = concat_ws(' ', p.n2, p.l2))
WHERE u.name = $1_user
GROUP BY p.publication_id