Help with complicated (to me) query....

[jetspin]

I'm having a difficult time writing an SQL statement. Hope I can explain it. (Or after reading this, should I just set a 'status' flag and avoid a complicated query? )

In my db we lease phone numbers.
There is a phone number table and an order table.
The phone number table has a one-many relationship to the order table. Phone number recs can exist without an order rec. When a phone number is leased, an order rec is created with a blank ordReturnDate. When the the phone number is returned, the ordReturnDate is filled in.
When the same phone number is leased again, a new order rec is created and the cycle continues. So a phone number rec can exist without any orders if it has never been leased and a phone number can have many orders if it has been leased. And (theoritically) only one of the orders can have a blank ordReturndate at a time. (In other words, a phone number can only be leased one order at a time.)

I want to write a query for when a new order is created that will list only available phone numbers to select from.

This would be (all phone numbers that have no orders at all) or (if the phone number has any orders there can be no orders with null ordReturnDate).

"Phone Numbers"
tblPhoneNumbers
pnrPnrID (PK)
pnrPhoneNumber

"Orders"
tblOrders
ordOrdID (PK)
ordPnrID (FK from pnrPnrID)
ordReturnDate

Thanks. Any help at all would be appreciated.

 

[swaybright]

If it is always true that any available number will have an (issue date AND a return date) OR (no issue date) you can query it without a flag.

In the QBE pane set the critera for these two fields as follows:
In the first row issue date -- "not null"; return date -- "not null"
In the second row (OR row) issue date -- "null"

HTH

Shane

 

[WB786]

Try this:

SELECT tblPhoneNumbers.pnrPnrID, tblPhoneNumbers.pnrPhoneNumber, tblOrders.ordOrdID, tblOrders.ordReturnDate, FROM tblPhoneNumbers LEFT JOIN tblOrders ON tblPhoneNumbers.ordPnrID = tblOrders.ordPnrID
WHERE (((tblOrders.ordReturnDate) Is Not Null));

You might need to modify it if you want to add more values to the query string.

WB

 

[JonFer]

You cannot just use swaybright's criteria - you'll get all phone number that have been leased and returned even if they are now still being used.

If you do a Left Join as wabeg posts and use instead the criteria that IssueDate Is Null, you'll get the phone numbers that have never been leased.

The other criteria should be that no order record for the phone number has a Null ReturnDate. There are several ways to do this. One is to define a field that uses a subquery to count the records with a Null ReturnDate and giving it a criteria that it equals 0.

 NullReturn: (Select Count(*) 
              From tblOrders as a
              Where a.ordPnrID=tblOrders.ordPnrID
                 and a.ReturnDate Is Null)

If you are using a Left Join, this may be the only criteria you need because I think it should return 0 for phone numbers without any orders, too.