Здравствуйте, corpse56, Вы писали:

C>MS SQL 2005

ну гдето так:

CREATE TABLE #DATAEXT
(
    ID       INT IDENTITY(1, 1),
    SORT     NVARCHAR(5),
    MNFIELD  NVARCHAR(5),
    MSFIELD  NVARCHAR(5),
    IDMAIN   INT
)
 
INSERT INTO #DATAEXT
  (
    SORT,
    MNFIELD,
    MSFIELD,
    IDMAIN
  )
SELECT 'A','200','$a',1
UNION ALL
SELECT 'AA','700','$a',1
UNION ALL
SELECT '11','899','$p',1
UNION ALL
SELECT '21','899','$p',1
UNION ALL
SELECT '31','899','$p',1
UNION ALL
SELECT 'B','200','$a',2
UNION ALL
SELECT 'BB','700','$a',2
UNION ALL
SELECT '41','899','$p',2
UNION ALL
SELECT '51','899','$p',2
UNION ALL
SELECT 'С','200','$a', 3
UNION ALL
SELECT 'СС','700','$a',3
UNION ALL
SELECT '51','899','$p',3
 
CREATE TABLE #DATAEXTPLAIN
(
    ID         INT IDENTITY(1, 1),
    PLAIN      NVARCHAR(5),
    IDMAIN     INT,
    IDDATAEXT  INT
)
     
INSERT INTO #DATAEXTPLAIN
  (
    PLAIN,
    IDMAIN,
    IDDATAEXT
  )
SELECT '51',3,12
UNION ALL
SELECT 'С,С',3,11
UNION ALL
SELECT 'С',3,10
UNION ALL
SELECT '51',2,9
UNION ALL
SELECT '41',2,8
UNION ALL
SELECT 'B,B',2,7
UNION ALL
SELECT 'B',2,6
UNION ALL
SELECT '31',1,5
UNION ALL
SELECT '21',1,4
UNION ALL
SELECT '11',1,3
UNION ALL
SELECT 'A,A',1,2
UNION ALL
SELECT 'A',1,1;
  
    WITH FC AS (SELECT dt.ID,
                       dt.MNFIELD,
                       dt.MSFIELD,
                       dt.IDMAIN,
                       dtp.PLAIN
                FROM   #DATAEXT dt
                       JOIN #DATAEXTPLAIN dtp
                            ON  dt.ID = dtp.IDDATAEXT)
SELECT COL1.PLAIN,
       COL2.PLAIN,
       COL3.PLAIN
FROM   FC COL1
       JOIN FC AS COL2
            ON  COL2.IDMAIN = COL1.IDMAIN
                AND COL2.MNFIELD = 700
                AND COL2.MSFIELD = '$a'
       JOIN FC AS COL3
            ON  COL3.IDMAIN = COL1.IDMAIN
                AND COL3.MNFIELD = 899
                AND COL3.MSFIELD = '$p'
WHERE  COL1.MNFIELD = 200
       AND COL1.MSFIELD = '$a'
ORDER BY
       COL1.PLAIN,
       COL3.PLAIN
   
DROP TABLE #DATAEXT
DROP TABLE #DATAEXTPLAIN

Пойдет?

P.S. Очень муторно таблички тест. данными забивать