Check and Retrieve those results where a cell(row) contains atleast one value from many values in a column

Question

Let me give you an example :

---

I have a table (name = posts) which contains following rows:

   id    |    title   | .... |  names
    1    |      .     |   .  |
    .    |      .     |   .  |
    4    |     ABC    |   .  |  joe  , nash  , brad
    5    |     XYZ    |   .  |  jeff , michael ,gerard, pique
    6    |     JKQ    |   .  |  archeo , michael ,neymar, schultz

---

Another table (name = followed) containing rows:

   id    |  names
    1    |  jeff
    2    |  michael
    3    |  cheryl
    4    |  zohan

---

Given : Two tables

To fetch : Titles from posts table where names.posts contains atleast one name from names.followed in followed table

Result I should get in the above example : XYZ , JKQ

query form : mysql , PDO

---

I dont know if its possible .The reason I am storing values in cells with names concatenated with commas is to reduce redundancy. I have basic query knowledge only .If its possible it will be great if some one can answer me a query/queries to get asked result . If not possible just give me an alternative method with a different table structure and an explanation why its the best alternative solution .

Big Thanks to all who can help me in any way possible

Answer 1

If I were doing this in SQL Server^*, I would use:

USE tempdb;

CREATE TABLE Posts
(
    PostID INT NOT NULL IDENTITY(1,1)
    , PostName VARCHAR(255)
    /*  whatever other fields relate to POSTS */
    , CONSTRAINT PK_Posts PRIMARY KEY CLUSTERED (PostID) 
);
CREATE TABLE People
(
    PeopleID INT NOT NULL IDENTITY(1,1)
    , PersonName VARCHAR(255)
    /* whatever other fields relate to PEOPLE */
    , CONSTRAINT PK_People PRIMARY KEY CLUSTERED (PeopleID) 
);
CREATE TABLE PostsPeopleXRef
(
    PostID INT NOT NULL 
    , PeopleID INT NOT NULL 
    , CONSTRAINT FK_PostsPeopleXRef_PostID 
        FOREIGN KEY (PostID) REFERENCES Posts(PostID)
    , CONSTRAINT FK_PostsPeopleXRef_PeopleID 
        FOREIGN KEY (PeopleID) REFERENCES People(PeopleID)
);

INSERT INTO Posts (PostName) VALUES ('Post Test 1');
INSERT INTO People (PersonName) VALUES ('Jack');
INSERT INTO People (PersonName) VALUES ('Jill');
INSERT INTO People (PersonName) VALUES ('John');
INSERT INTO PostsPeopleXRef (PostID, PeopleID) VALUES (1, 1);
INSERT INTO PostsPeopleXRef (PostID, PeopleID) VALUES (1, 2);
INSERT INTO PostsPeopleXRef (PostID, PeopleID) VALUES (1, 3);

SELECT *
FROM dbo.Posts;

SELECT *
FROM dbo.People;

SELECT P.PostName, E.PersonName
FROM dbo.PostsPeopleXRef X
    INNER JOIN dbo.Posts P ON X.PostID = P.PostID
    INNER JOIN dbo.People E ON X.PeopleID = E.PeopleID
ORDER BY P.PostName
    , E.PersonName;

This results in the following output:

* : for MySQL, you'll need some changes, like replacing IDENTITY(1,1) with AUTO_INCREMENT, removing the CLUSTERED keyword, adding the ENGINE = InnoDB at the CREATE TABLE statements, etc.

Answer 2

To answer your first question of "is it possible?" The answer is yes although I agree with the others' advice about a 3rd table that links posts to people.

SQL Fiddle

SELECT DISTINCT a.id, a.title, a.names 
FROM Posts a 
JOIN Followed b ON a.names LIKE  concat(concat('%', b.names), '%');