Need help with complicated query!

[gfokke]

Hi People,

I need some help with a SQL query... I have a number of tables in my database and I would like to be able to filter out just 1 list with certain information:

The 2 most important tables are:

Table: users
Columns: name, age, gender
data example: Anaticula87, 22, m
data example: Tweeke, 25, m
data example: augustusmeisje, 40, f

Table: weather
Columns: date, hour, temp, clouds, rain, snow
data example: 20020101, 1, -1, 4, 0, 1
data example: 20020605, 4, 135, 2, 0, 0
data example: 20060213, 23, 46, 6, 1, 0

Then a bit more complicated... I have a table for every user, named 'playlist_' + 'username'.

Table: playlist_anaticula87
Columns: UTSID, date, hour, artist, song, tag1, tag2
data example: 1183449088, 20070703, 2, The Chemical Brothers, The Big Jump, electronic, rock
data example: 1183413662, 20070703, 3, The Chemical Brothers, Come Inside, electronic, rock
data example: 1183182450, 20070630, 8, Incubus, When It Comes, rock, indie

So my goal is:

Filter out a list based on gender, age, time and weather conditons with a combined count for every genre (tag1&tag2)

For example: Get list for Female users, aged 25 when it was raining (rain = 1)

Result:

genre                    plays
-------------------------------------------------
Rock                     10230
Jazz                     2765
RnB                      89
Electronic               728
etc
etc

Now I'm very new to SQL so I find this quite hard.. I hope to be able to do this in 1 query that I can send with PHP..

But any solutions to this are more than welcome.. Whoever can help me with this.. I owe you HUGE!

Thanks!

[Atli]

Hey.

There is one huge flaw in your database design, which - amongst other things - makes the query you are trying to build much more complicated than it needs to be.

Your "playlist_<username>" tables. As a general, and rather important, rule-of-thumb: you should never have to modify the table structure to accommodate new data. - In your case, because your playlist tables are named after your users, it is practically impossible to dynamically search all the various playlist_user tables without manually editing the queries to insert the names.

What you should be doing is creating one big playlist table, adding the ID of the user as a foreign key to link each row to it's respective user.

+---------------+    +-----------------+
| user          |    | playlist        |
+---------------+    +-----------------+
| id (PK)       |1--N| user_id (FK)    |
| name          |    | UTSID (PK)      |
| etc...        |    | date            |
+---------------+    | hour            |
                     | artist          |
                     | song            |
                     | tag1            |
                     | tag2            |
                     +-----------------+

See what I mean?
There you would have all the playlist data in a single table, which simplifies the query you are asking about immensely.

It may look something in the direction of:

SELECT
        pl.genre,
        COUNT(*) AS 'plays'
FROM    playlist AS pl
INNER JOIN weather AS w
        ON w.date = '<insert date>'
        AND w.hour = 12
        AND w.rain = 1
INNER JOIN users AS u
        ON u.age = 25
        AND u.gender = 'm'
GROUP BY pl.genre

(Disclaimer: this was written after being awake for ~23 hours, and after being coffein deprived for the last 10 hours, so this -probably- won't work out-of-the-box xD)


P.S.
Also, note that your "tag1" and "tag2" fields violate the 1NF (First Normal Form): you should not have multiple columns in a single table that store the same type of data.

What would happen, for example, if in the future you needed to add "tag3"? You would have to modify all the preexisting data to add the third tag, and every row would HAVE TO have 3 tags. - Ideally, you should be able to add as many, or as few, tags per row as is needed, and not a single, fixed amount. To fix this, you would pull the "tagN" fields out of the table and into a separate table.

+---------------+    +-----------------+    +----------------+
| user          |    | playlist        |    | tag            |
+---------------+    +-----------------+    +----------------+
| id (PK)       |1--N| user_id (FK)    |    | tag_name (PK)  |
| name          |    | UTSID (PK)      |1--N| UTSID (FK, PK) |
| etc...        |    | date            |    +----------------+
+---------------+    | hour            |
                     | artist          |
                     | song            |
                     +-----------------+

Truth be told, there is a more "normalized" design for this situation, but that's a different conversation. This should do fine to begin with.

Also, both the "artist" and "song" fields should ideally be in their own tables to. The "artist" is not really dependent on the ID of the playlist; it's fairly independent in that context. Similarly, the "song" relies on the "artist", and not the "playlist", and as such does not belong in the "playlist" table.

... But that is a discussion for another time. For now your goal can be achieved using the structre I suggested. - Best to put this stuff on the TODO list :)

P.P.S
What sort of fields are your "date" and "hour" fields? They look sort of like strings, based on you example data. Is that so?
If it is, you really should check out MySQL's Date and Time types. Will most likely save you a ton of working in the future: to get used to them sooner rather than later.