Many-to-many relationship SELECT issue

I have a very annoying problem on the application I've been contracted to work in.

The database structure, which I can't modify for compatibility reasons is a mess. It's basically a many-to-many-to-many-to-many-to-bang-your-head-on-the-desk relationship. It goes like this:

contact

---------------------------
| contact_id | name | ... |
---------------------------
| 1          | foo  |     |
---------------------------

metadefinition

-----------------------------
| metadefinition_id | name  |
-----------------------------
| 1                 | title |
-----------------------------
| 2                 | job   |
-----------------------------

contact_metadata

----------------------------
| contact_id | metadata_id |
----------------------------
| 1          | 1           |
----------------------------
| 1          | 2           |
----------------------------

metadata

-------------------------------------------
| metadata_id | metadefinition_id | value |
-------------------------------------------
| 1           | 1                 | mrs   |
-------------------------------------------
| 2           | 2                 | coder |
-------------------------------------------

So, for a single contact, I want to search on all this and obtain something like this:

-----------------------------------------------------
| contact_id | name | metadata.title | metadata.job |
-----------------------------------------------------
| 1          | foo  | mrs            | coder        |
-----------------------------------------------------

So now what I've tried so far. I can fetch the list of metadefinition in advance, that's not really an issue. So I build a query like this:

SELECT contact.*,m1.value AS `metadata.title` FROM contact
   LEFT JOIN contact_metadata ON contact.contact_id = contact_metadata.contact_id
   LEFT JOIN metadata m1 ON contact_metadata.metadata_id = m1.metadata_id AND m1.metadefinition_id = 1
  开发者_如何学JAVA GROUP BY contact_id

This works for a single metadefinition, I get something like this:

--------------------------------------
| contact_id | name | metadata.title |
--------------------------------------
| 1          | foo  | mrs            |
--------------------------------------

If I try with two, however:

SELECT contact.*,m1.value AS `metadata.title`,m2.value AS `metadata.job` FROM contact
   LEFT JOIN contact_metadata ON contact.contact_id = contact_metadata.contact_id
   LEFT JOIN metadata m1 ON contact_metadata.metadata_id = m1.metadata_id AND m1.metadefinition_id = 1
   LEFT JOIN metadata m2 ON contact_metadata.metadata_id = m2.metadata_id AND m2.metadefinition_id = 2
   GROUP BY contact_id

I get:

-----------------------------------------------------
| contact_id | name | metadata.title | metadata.job |
-----------------------------------------------------
| 1          | foo  | mrs            | NULL         |
-----------------------------------------------------

If I remove the GROUP BY clause, of course, I get:

-----------------------------------------------------
| contact_id | name | metadata.title | metadata.job |
-----------------------------------------------------
| 1          | foo  | mrs            | NULL         |
-----------------------------------------------------
| 1          | foo  | NULL           | coder        |
-----------------------------------------------------

I'm open to anything as long as the query times are relatively acceptable (considering the structure, if it takes 10 seconds for 100000 records it's better than nothing)

Will it be temporary tables, stored procedures, I don't care, as long as I don't have to change the actual database structure.

Trust me, when it'll be the time to remodel, it'll be my pleasure to take this whole thing down.

Is there a solution to this, is it possible?

Thanks in advance.

I'd use a pair of correlated sub-queries. Something like

SELECT c.*
  ,( Select value from metadata m1 
       INNER JOIN contact_metadata cm 
       on cm.metadata_id = m1.metadata_id 
     WHERE m1.metadefinition_id = 1 
       AND cm.contact_id = c.contact_id )AS `metadata.title`
  ,( Select value from metadata m1 
       INNER JOIN contact_metadata cm 
       ON cm.metadata_id = m1.metadata_id 
     WHERE m1.metadefinition_id = 2 
       AND cm.contact_id = c.contact_id )AS `metadata.job`
FROM contact c
WHERE c.contact_id = 1

You'll need to fetch each metadata item (title, job etc.) as a separate subquery, and then join on contact_id to bring these metadata items together for each contact. Like this:

SELECT contact.*, metadata_title, metadata_job FROM contact 
INNER JOIN 
(SELECT contact_id,m.value AS `metadata_title` FROM contact_metadata 
   LEFT JOIN metadata m ON contact_metadata.metadata_id = m.metadata_id AND m.metadefinition_id = 1) metaTitle ON metaTitle.contact_id=contact.contact_id
INNER JOIN 
(SELECT contact_id,m.value AS `metadata_job` FROM contact_metadata 
   LEFT JOIN metadata m ON contact_metadata.metadata_id = m.metadata_id AND m.metadefinition_id = 2) metaJob ON (metaJob.contact_id=contact.contact_id)

If you want to allow for the possibility of a client not having a particular metadata item, use LEFT JOINs instead, as at present, the query will only return clients that have both a job and a title defined.

Now that I've finally read the question properly, here's my two cents, for what's it's worth (no pun intended).

SELECT contact.contact_id, contact.name, m1.value AS 'metadata.title', m2.value AS 'metadata.job'     
FROM dbo.contact
INNER JOIN dbo.contact_metadata cm1 ON cm1.contact_id = cm1.contact_id
INNER JOIN dbo.metadata m1 ON cm1.metadata_id = m1.metadata_id AND m1.metadefinition_id = 1
INNER JOIN dbo.contact_metadata cm2 ON contact.contact_id = cm2.contact_id
INNER JOIN dbo.metadata m2 ON cm2.metadata_id = m2.metadata_id AND m2.metadefinition_id = 2