Help with a couple of Queries

[Joe Cilinceon]

I'm trying to find out if a tenant still rents a unit. Now I have 2 tables
one is Tenants with CustNo that ties it to the Leases table. Now a tenant
can have more than one unit and I'm want only those tenants that no longer
rents.

TENANT
CustNo

LEASES
UnitNo
EndDate (will be null if rented or a date if no longer rented)

 

[Tom Ellison]

Dear Joe:

How about:

SELECT T.CustNo
FROM TENANT T
LEFT JOIN (SELECT CustNo FROM LEASES
WHERE L.EndDate IS NOT NULL) L
ON L.CustNo = T.CustNo
WHERE L.CustNo IS NULL

This basically says to look for all tenants who have no unexpired leases.
It uses a subquery to return all unexpired leases.

Tom Ellison

 

[Guest]

How do you tie the TENANT table to the LEASES table?

Your LEASES table must have CustNo also.

 

[Joe Cilinceon]

Yes, CustNo is a field in Leases as well as Tenants. Sorry about that, I
assumed that was understood based on what I said in the text.

 

[Joe Cilinceon]

Thanks for responding Tom, but I'm looking for tenants that are no longer
renting any units. I'm not sure I'm following the logic with finding the
tenants that still have units.

 

[John Spencer]

SELECT CustNo
FROM Tenant
WHERE CustNo in
(SELECT CustNo
FROM Leases
WHERE EndDate is Null)

 

[Joe Cilinceon]

Let me try this again. I have the 2 tables below. CustNo is the link between
tables.

TENANTS (one)
CustNo

LEASES (many)
CustNo
UnitNo
EndDate (is null if rented or has date if no longer rented)

I'm want to produce a list of those tenants that no longer rent any unit.

 

[Tom Ellison]

Dear Joe:

As I stated below, the query I wrote will find tenants who have no unexpired
leases. That is the same thing as saying "no longer renting" isn't it?

Tom Ellison

 

[Joe Cilinceon]

Thanks John but this tells me if a tenant still has a unit. Keep in mind a
single tenant can have several units rented or not rented. I'm looking for
only those that have no units at all rented.

 

[John Spencer]

Rereading the thread, I find you want tenants that NO LONGER have a unit.
One minor change.

SELECT CustNo
FROM Tenant
WHERE CustNo NOT in
(SELECT CustNo
FROM Leases
WHERE EndDate is Null)

 

[Joe Cilinceon]

Sorry Tom I'm not following here, as I'm looking for tenants where all
leases are expired.

 

[Joe Cilinceon]

Yes. that got it John thanks.

Rereading the thread, I find you want tenants that NO LONGER have a
unit. One minor change.

SELECT CustNo
FROM Tenant
WHERE CustNo NOT in
(SELECT CustNo
FROM Leases
WHERE EndDate is Null)

 

[Tom Ellison]

Dear Joe:

The query I gave will show all tenants where all leases are expired, and
also any tenants who have never had a lease.

Tom Ellison

 

[Tom Ellison]

Dear Joe:

By the way, the query John gave you will also show any tenants who have
never had a lease. In many cases, the LEFT JOIN approach is faster, so
that's why I suggested it.

Tom Ellison

Dear Joe:

The query I gave will show all tenants where all leases are expired, and
also any tenants who have never had a lease.

Tom Ellison

 

[Joe Cilinceon]

Thanks Tom I do appreciate the help. I just wasn't following what you
posted. Querys confuse me at best sometimes.