Inner join, subselect, xml, or what to use?

Transact-SQL
1

Here is my tables and the result i need:

Pasted image649×636 10 KB

I have tried and tried and so on, but now i´m so confused that i don´t know anymore.
Is there anyone who maybe can assist on this?

2

It would have been nice to see some of what you have done.

SELECT M.Tele, M.[F-namn], M.[E-namn], a1.[misc-text] AS miscI2, a2.[misc-text] AS miscI3
FROM main M 
INNER JOIN conn c ON M.tele = c.Phone 
INNER JOIN addr a1 ON c.Raw_id = a1.Recl_id AND a1.misc = 'I2' 
INNER JOIN addr a2 ON c.Raw_id = a2.Recl_id AND a2.misc = 'I3';

Now do you understand what I have done?

3

Hi,
Sorry for that.
But i see one misstake in my picture, the Raw_id is just a number, it is not in any way in any relation with Red_id or Rec_id.
So there is a relation Tele to Phone and Rec_id and Red_id.
Conversion failed when converting the varchar value 'I2' to data type int.