Соединение таблиц с условием

Question

Имеется два списка. Нужно их совместить. Пробовал

SELECT DISTINCT z.name, n.name
FROM Zheni z, Nevest n
WHERE (z.vozr BETWEEN n.minvozr AND n.maksvozr) AND (z.ves BETWEEN n.minves and n.maxves) AND (z.rost BETWEEN n.minrost and n.maxrost)
      AND (n.vozrast BETWEEN z.minvozr AND z.maxvozr) AND (n.ves BETWEEN z.minves and z.maxves) AND (n.rost BETWEEN z.minrost and z.maxrost)
ORDER BY z.name, n.name

Что должно быть: Алекс Анна, Алексей Елена.

Answer 1

SELECT TOP 1 WITH TIES
        z.name AS ZheniName,
        n.name AS NevestName
FROM Zheni AS z
CROSS JOIN Nevest AS n
WHERE   (z.vozr     BETWEEN n.minvozr   AND n.maksvozr)
    AND (z.ves      BETWEEN n.minves    AND n.maxves)
    AND (z.rost     BETWEEN n.minrost   AND n.maxrost)
    AND (n.vozrast  BETWEEN z.minvozr   AND z.maxvozr)
    AND (n.ves      BETWEEN z.minves    AND z.maxves)
    AND (n.rost     BETWEEN z.minrost   AND z.maxrost)
ORDER BY ROW_NUMBER() OVER(PARTITION BY z.name ORDER BY n.name)

БЛОГ НА HUSL

Соединение таблиц с условием

Ответы (1 шт):