Matching delimited string to table rows

Question

So I have two tables in this simplified example: People and Houses. People can own multiple houses, so I have a People.Houses field which is a string with comma delimeters (eg: "House1, House2, House4"). Houses can have multiple people in them, so I have a Houses.People field, which works the same way ("Sam, Samantha, Daren").

I want to find all the rows in the People table corresponding to the the names of people in the given house, and vice versa for houses belong to people. But I can't figure out how to do that.

This is as close as I've come up with so far:

   SELECT People.* 
     FROM Houses 
LEFT JOIN People ON Houses.People Like CONCAT(CONCAT('%', People.Name), '%')
    WHERE House.Name = 'SomeArbitraryHouseImInterestedIn'

But I get some false positives (eg: Sam and Samantha might both get grabbed when I just want Samantha. And likewise with House3, House34, and House343, when I want House343).

I thought I might try and write a SplitString function so I could split a string (using a list of delimiters) into a set, and do some subquery on that set, but MySQL functions can't have tables as return values.

Likewise you can't store arrays as fields, and from what I gather the comma-delimited elements in a long string seems to be the usual way to approach this problem.

I can think of some different ways to get what I want but I'm wondering if there isn't a nice solution.

Answer 1

The nice solution is to redesign your schema so that you have the following tables:

People
------
PeopleID (PK)
...


PeopleHouses
------------
PeopleID (PK) (FK to People)
HouseID (PK) (FK to Houses)


Houses
------
HouseID (PK)
...

Answer 2

Likewise you can't store arrays as fields, and from what I gather the comma-delimited elements in a long string seems to be the usual way to approach this problem.

I hope that's not true. Representing "arrays" in SQL databases shouldn't be in a comma-delimited format, but the problem can be correctly solved by using a junction table. Comma-separated fields should have no place in relational databases, and they actually violates the very first normal form.

You'd want your table schema to look something like this:

CREATE TABLE people (
   id int NOT NULL,
   name varchar(50),
   PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE houses (
   id int NOT NULL,
   name varchar(50),
   PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE people_houses (
   house_id int,
   person_id int,
   PRIMARY KEY (house_id, person_id),
   FOREIGN KEY (house_id) REFERENCES houses (id),
   FOREIGN KEY (person_id) REFERENCES people (id)
) ENGINE=INNODB;

Then searching for people will be as easy as this:

SELECT  p.* 
FROM    houses h
JOIN    people_houses ph ON ph.house_id = h.id
JOIN    people p ON p.id = ph.person_id
WHERE   h.name = 'SomeArbitraryHouseImInterestedIn';

No more false positives, and they all lived happily ever after.

Answer 3

The problem is that you have to use another schema, like the one proposed by @RedFilter. You can see it as:

People table: PeopleID otherFields

Houses table: HouseID otherFields

Ownership table: PeopleID HouseID otherFields

Hope that helps,

Answer 4

Short Term Solution

For your immediate problem, the FIND_IN_SET function is what you want to use for joining:

For People

SELECT p.*
  FROM PEOPLE p
  JOIN HOUSES h ON FIND_IN_SET(p.name, h.people)
 WHERE h.name = ?

For Houses

SELECT h.*
  FROM HOUSES h
  JOIN PEOPLE p ON FIND_IN_SET(h.name, p.houses)
 WHERE p.name = ?

Long Term Solution

Is to properly model this by adding a table to link houses to people, because you're likely storing redundant relationships in both tables:

CREATE TABLE people_houses (
  house_id int,
  person_id int,
  PRIMARY KEY (house_id, person_id),
  FOREIGN KEY (house_id) REFERENCES houses (id),
  FOREIGN KEY (person_id) REFERENCES people (id)
)

Answer 5

Hi you just change the table name places, left side is People and then right side is Houses:

SELECT People.* FROM People LEFT JOIN Houses ON Houses.People Like CONCAT(CONCAT('%', People.Name), '%') WHERE House.Name = 'SomeArbitraryHouseImInterestedIn'