How do I select only one column result based on its value from a transnational table?

Question

I have a number of items that are for rent in an item table. I have a transnational table that shows the rental history on each item in my item table. If the item is currently not being rented then all entries in the "Availability Status" column will say 'closed'. If the item is being rented one of the fields in the "Availability Status" column will say "ONRENT". I want to write a query that will list each rental item and return whether they are currently "ONRENT" (Not Available) or "CLOSED" (Available). I have written a query that returns rental items that are "ONRENT" since my WHERE clause can find the only row containing "ONRENT". Not sure how to return available items when all the rows in the "Availability Status" column say "CLOSED" Here is my successful query for ONRENT items.

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%'

Correction...the above query returns items that are onrent because there is a rental contract that exists in the rh.[Rental Header] Table. I need to find items that are not onrent that do not have a rental contract present in the rh.[Rental Header] table. — Craig Zirnheld, Jun 04 '14 at 14:29
Still relatively new but can you expound on the basic difference? — Craig Zirnheld, Jun 04 '14 at 14:31
You'll have to excuse me. Your question uses the word transnational but I think you mean transactional. — Jodrell, Jun 04 '14 at 14:36

score 0 · Accepted Answer · answered Jun 04 '14 at 14:32

I simplified your use case a little, for sake of clarity. What you seem to be looking for is something like this:

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)

I should add there are many ways to solve this problem, some of which may be more efficient than this one.

Worked perfectly. I knew they're would be a union involved. Just did'nt think about distinct. Thanks. — Craig Zirnheld, Jun 04 '14 at 14:46

Jodrell · Answer 2 · 2014-06-04T15:03:02.067

if you simply switch your join,

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%';

Then you'll get all the [LIVE$Rental Header], you'll only get [LIVE$Rental Line] data for related rows that are 'ONRENT', as well as your other conditions.

How do I select only one column result based on its value from a transnational table?

2 Answers2