UNION SELECT with multiple distinct criteria where there is no real FK

I need to select a handfull of items from several tables with one problematic criterium. There is a meta_data table that contains information about a referred table. Simplified structure:

table quest
id   INT AI PK
id_questtype   INT  FK -> questtype.id
id_creator   INT  FK -> user.id
creationdate   DATETIME
...

Each id refers to respective table

table user
id   INT AI PK
id_group   INT
username   VARCHAR(255)
...

table questtype
id   INT AI PK
name   VARCHAR(100)
...

Now comes tricky part:

table  quest_meta
id   INT AI PK
id_quest   INT  FK -> quest.id
id_user   INT  FK -> user.id
id_meta   INT NN
key   VARCHAR(20)
value   LONGTEXT

This table "quest_meta" is used to store all sorts of additional information for each quest item. For example question 1, question 2 etc.

table participation
id_user   INT  FK -> user.id
id_quest   INT  FK -> quest.id
id_quest_meta   INT NN

The id_quest_meta refers to quest_meta.id_meta but its not a real FK because there can be multiple rows with the same id_meta in the quest_meta table.

Works all nice and fine until i want to query information join all of those tables and having to fulfill !2! criteria in the participation table.

My current query looks something like this:

SELECT up.time, qm.value as title, u.username, u.id as id_user, q.id as id_quest
FROM participation p INNER JOIN quests q 
ON p.id_quest = q.id INNER JOIN user u
ON p.id_user = u.id INNER JOIN quest_meta qm
ON q.id = qm.id_quest
WHERE up.time > '2011-08-19 00:00:00' AND qm.key = 'quest_title' AND qm.id_meta = -1
GROUP BY p.id_user
UNION
SELECT p.time, qm.value as title, u.username, u.id as id_user, q.id as id_quest
FROM participation p INNER JOIN quests q 
ON p.id_quest = uq.id INNER JOIN users u
ON p.id_user = u.id INNER JOIN quests_meta qm
ON q.id = qm.id_quest
WHERE p.time > '2011-08-19 00:00:00' AND q.id = 2 AND p.vote = '1' AND p.id_quest_meta = -7
GROUP BY id_user 
ORDER BY time DESC;

The reason for the UNION is, that i need the additional criterium p.vote = '1' together with p.id_quest_meta = -7 (this is the quest_meta i know means it should be shown). So this is what i have...

MY PROBLEM NOW IS: How can i additionally make sure, that FROM 2nd SELECT only rows are selected WHERE qm.key = 'quest_title' AND qm.id_quest_meta = -1

Currently i receive a random qm.value from all the rows that are in the participation table.

All t开发者_JAVA技巧ables are InnoDB. If anything is unclear, ill love to explain whatever details are missing. Thanks a lot in advance.

---

I found the solution over a good night of sleep and dreaming about it :)

The solution is, to put the second SELECT in a subselect without the GROUP BY which allows me to SELECT "WHERE criterium 2" from the rows that fulfill criterium 1 and then GROUP the result and making UNION with first SELECT.

Here is the query:

SELECT up.time, qm.value as title, u.username, u.id as id_user, q.id as id_quest, qm.key as tit
FROM participation p INNER JOIN quests q 
ON p.id_quest = q.id INNER JOIN user u
ON p.id_user = u.id INNER JOIN quest_meta qm
ON q.id = qm.id_quest
WHERE p.time > '2011-08-19 00:00:00' AND qm.key = 'quest_title' AND qm.id_meta = -1
GROUP BY p.id_user
UNION
SELECT a.time, a.type, a.title, a.username, a.id_user, a.id_quest, a.tit
FROM (
SELECT p.time, qm.value as title, u.username, u.id as id_user, q.id as id_quest, qm.tit
FROM participation p INNER JOIN quests q 
ON p.id_quest = uq.id INNER JOIN users u
ON p.id_user = u.id INNER JOIN quests_meta qm
ON q.id = qm.id_quest
WHERE p.time > '2011-08-19 00:00:00' AND q.id = 2 AND p.vote = '1' AND p.id_quest_meta = -7
) AS a
WHERE a.tit = 'quest_title'
GROUP BY id_user 
ORDER BY time DESC;

The only downside is, that i had to add one more column (qm.tit) to the result table because i needed to query for it in the "select from subselect" (WHERE a.tit = 'quest_title').