MYSQL - Help with a more complicated Query

Question

I have two tables:

tbl_lists and tbl_houses

Inside tbl_lists I have a field called HousesList - it contains the ID's for several houses in the following format:

1# 2# 4# 51# 3#

I need to be able to select the mysql fields from tbl_houses WHERE ID = any of those ID's in the list. More specifically, I need to SELECT SUM(tbl_houses.HouseValue) WHERE tbl_houses.ID IN tbl_lists.HousesList -- and I want to do this select to return the SUM for several rows in tbl_lists.

Anyone can help? I'm thinking of how I can do this in a SINGLE query since I don't want to do any mysql "loops" (within PHP).

Answer 1

If your schema is really fixed, I'd do two queries:

SELECT HousesList FROM tbl_lists WHERE ... (your conditions)

In PHP, split the lists and create one array $houseIDs of IDs. Then run a second query:

SELECT SUM(HouseValue) FROM tbl-Houses WHERE ID IN (.join(", ", $houseIDs).)

I still suggest changing the schema into something like this:

CREATE TABLE tbl_lists (listID int primary key, ...)
CREATE TABLE tbl_lists_houses (listID int, houseID int)
CREATE TABLE tbl_houses (houseID int primary key, ...)

Then the query becomes trivial:

SELECT SUM(h.HouseValue) FROM tbl_houses AS h, tbl_lists AS l, tbl_lists_houses AS lh WHERE l.listID = <your value> AND lh.listID = l.listID AND lh.houseID = h.houseID

Storing lists in a single field really prevents you from doing anything useful with them in the database, and you'll be going back and forth between PHP and the database for everything. Also (no offense intended), "my project is highly dynamic" might be a bad excuse for "I have no requirements or design yet".

Answer 2

normalise http://en.wikipedia.org/wiki/Database_normalization