Why does this query only select a single row?

Question

SELECT * FROM tbl_houses 
WHERE 
 (SELECT HousesList 
  FROM tbl_lists 
  WHERE tbl_lists.ID = '123') LIKE CONCAT('% ', tbl_houses.ID, '#')

It only selects the row from tbl_houses of the last occuring tbl_houses.ID inside tbl_lists.HousesList

I need it to select all the rows where any ID from tbl_houses exists within tbl_lists.HousesList

Answer 1

Is that a database in zeroth normal form I smell?

If you have attributes containing lists of values, like that HousesList attribute, you should instead be storing those as distinct values in a separate relation.

CREATE TABLE house (
    id VARCHAR NOT NULL,
    PRIMARY KEY (id)
    );

CREATE TABLE list (
    id VARCHAR NOT NULL,
    PRIMARY KEY (id),
    );

CREATE TABLE listitem (
    list_id VARCHAR NOT NULL,
        FOREIGN KEY list_id REFERENCES list (id),
    house_id VARCHAR NOT NULL,
        FOREIGN KEY house_id REFERENCES house (id),
    PRIMARY KEY (list_id, house_id)
    );

Then your distinct house listing values each have their own tuple, and can be selected like any other.

SELECT house.*
FROM house
    JOIN listitem
        ON listitem.house_id = house.id
WHERE
    listitem.list_id = '123'

Answer 2

It's hard to tell without knowing exactly what your data looks like, but if it only matches the last ID, it's probably because you don't have any % at the end of the string, so as to allow for the list to continue after the match.