Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

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

share|improve this question

marked as duplicate by ypercube, Max Vernon, Phil, RolandoMySQLDBA Jun 12 at 15:34

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

3  
1  
@zword - imagine 3 tables: Posts -> PostsPeople -> People. PostsPeople contains PostID and PeopleID. –  Max Vernon Jun 12 at 13:20
1  
Yes CREATE TABLE POST(id int, ..., name varchar(25)); INSERT INTO POST VALUES (1, 'joe'); INSERT INTO POST VALUES(1, 'nash'); INSERT INTO POST VALUES(1, 'brad'); INSERT INTO POST VALUES(2, 'jeff')... &c. Better would be to just put in the id from the NAME table - joining made easier (note singular names for tables). PK on the two ids - or if you want to keep the text name, PK on id and name in POST. It's a joining table - classic db design pattern. –  Vérace Jun 12 at 13:20
2  
Similar question: Many To Many Relationships. The answer there - a junction (or intersection) table - is what @MaxVernon suggested. Try it. –  ypercube Jun 12 at 13:27
1  
@MaxVernon ok post me the correct table structure I should use using my example values and any query language u prefer –  Zword Jun 12 at 13:47
show 6 more comments

2 Answers 2

up vote 3 down vote accepted

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:

enter image description here

* : 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.

share|improve this answer
    
thanks @ypercube for the edit! –  Max Vernon Jun 12 at 17:20
1  
thanks max .Helped me a lot –  Zword Jun 12 at 17:48
add comment

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), '%');
share|improve this answer
1  
Possible, sure. Advisable, certainly not. Scalable, certainly not. –  Max Vernon Jun 12 at 13:47
    
I totally agree with you Max. –  JChao Jun 12 at 13:48
1  
Prone to error, yes. Will identify jeff as jeffrey, brad as bradley, mary as mary-jane, nick as nicky, ... –  ypercube Jun 12 at 13:51
    
It does give the answer for what I asked but doing this is not good as others said and I want the best possible method.So I will give you a +1 for aswering for what I asked –  Zword Jun 12 at 13:51
2  
@Jchao: I should have used Eve as Steve for dramatic effect. You can also use FIND_IN_SET() function if there are no spaces between names but only a comma, much better than LIKE/CONCAT. –  ypercube Jun 12 at 14:14
show 2 more comments

Not the answer you're looking for? Browse other questions tagged or ask your own question.