Как выбрать только один результат столбца на основе его значения в транснациональной таблице?

Question

У меня есть несколько предметов, которые сдаются в аренду в таблице предметов. У меня есть транснациональная таблица, которая показывает историю аренды каждого предмета в моей таблице предметов. Если элемент в настоящее время не сдается в аренду, тогда все записи в столбце "Статус доступности" будут иметь статус "закрыто". Если объект находится в аренде, одно из полей в столбце "Статус доступности" будет отображаться как "ONRENT". Я хочу написать запрос, в котором будет перечислен каждый пункт проката и возвращено, являются ли они в настоящее время "ONRENT" (недоступно) или "CLOSED" (доступно). Я написал запрос, который возвращает пункты проката, имеющие значение "ONRENT", поскольку мое предложение WHERE может найти единственную строку, содержащую "ONRENT". Не уверен, как вернуть доступные элементы, когда все строки в столбце "Статус доступности" говорят "ЗАКРЫТО". Вот мой успешный запрос для ONRENT элементов.

select      rl.[No_],
        rl.[Manufacturer Code],
        rl.[Model Code],
        rl.[Availability Status],
        rl.[Start Date],
        rh.[Order Date],
        rh.[No_],
        rh.[Ship-to Name],
        rh.[Ship-to Address]

from        [LIVE$Rental Line]rl left outer join [LIVE$Rental Header]rh 
on rl.[Document No_] = rh.[No_]
where       rl.[Availability Status] = 'ONRENT'
and         rl.[No_] not like 'SR%'

Answer 1

Я немного упростил ваш вариант использования, для ясности. То, что вы, похоже, ищете, выглядит примерно так:

SELECT distinct no, 'CLOSED' FROM table1 t1
WHERE EXISTS (SELECT * FROM table1 WHERE status = 'CLOSED' AND no = t1.no)
UNION
SELECT distinct no, 'OPEN' FROM table1 t2
WHERE NOT EXISTS (SELECT * FROM table1 WHERE status = 'CLOSED' AND no = t2.no) 

Я должен добавить, что есть много способов решить эту проблему, некоторые из которых могут быть более эффективными, чем этот.

Answer 2

Если вы просто поменяете соединение,

SELECT
            rl.[No_],
            rl.[Manufacturer Code],
            rl.[Model Code],
            COALESCE(rl.[Availability Status], 'CLOSED') [Availability Status],
            rl.[Start Date],
            rh.[Order Date],
            rh.[No_],
            rh.[Ship-to Name],
            rh.[Ship-to Address]
    FROM
            [LIVE$Rental Header] rh
        LEFT OUTER JOIN
            [LIVE$Rental Line] rl  
                ON
                        rl.[Document No_] = rh.[No_]
                    AND
                        rl.[Availability Status] = 'ONRENT'
    WHERE
            rl.[No_] not like 'SR%';

Тогда вы получите все [LIVE$Rental Header], вы получите только [LIVE$Rental Line] данные для связанных строк, которые 'ONRENT', а также ваши другие условия.