How to join a number of fields from one table and compare the result with another field from another table?

Question

I have the following situation:

table 1 -> publications

table 2 -> users

In table one among some details of publications, I have 20 fields concerning authors, exactly their names and lastnames (these fields: n1, l1, n2, l2, n3, l3... and so on).

In table two I have one field that contains the name and the lastname of user.

I would like to display only these publications where the name and the lastname from table 1 will be the same as the name and the lastname from table 2.

Here's my query so far:

$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."')";

I know that probably my way of thinking is wrong. Could you help me? I will be grateful if you would give me some advice.

Answer 1

You should remove the authors from the Publications table and make a 3rd table that links Users with Publications.

PublicationAuthors

PublicationID  int
UserID int

Then your queries can just check that table to see if a user is associated with the publication. Plus, you can have as many authors as needed without adding new columns to Publications and if someone changes their name it won't break the relationship with Publications.

Here is an example (I used SQL Server for this so there might be small syntax differences):

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

Then when you realize I misspelled the author's last name, you can just update that one row without touching the Publications table.

Answer 2

You might find it easier to setup your user table to contain an user_id field as the primary key and use that as an index in your publication table.

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

Or you can just perform a join like this:

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