complex select query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All....I have a complex problem I am trying to solve. Any help you can
provide would be greatly appreciated. Below is an illustration to help you
understand.

TblCasIDDetails tblRental
CasID Casid Returned
1 1 Y
2 2 Y
3 3 N
4 4 N
5 2 Y
6 1 N

What I am trying to do is fetch the CasID from tblCasIDDetails but only the
ones that are not rented. The second tblrental stores the casids that have
been rented or returned. So for the above example my output should include
2,5,6 because 5 and 6 have not been rented and 2 was rented but the return
flag is set to Y. While 1 was rented and returned but has been rented again
(last record). I tried every type of join but it doesn't work. The CasID is
unique field. Please help.
 
I would suggest that you consider a UNION query to deal with the two
scenarios e.g.
SELECT TblCasIDDetails.CasId
FROM TblCasIDDetails LEFT JOIN tblRental ON TblCasIDDetails.CasId =
tblRental.casId
WHERE tblRental.casId IS NULL
UNION
SELECT TblCasIDDetails.CasId
FROM TblCasIDDetails INNER JOIN tblRental ON TblCasIDDetails.CasId =
tblRental.casId
WHERE tblRental.returned = "Y"

Hope This Helps
 
You should be careful with putting this kind of table structure in place
that you're not overcomplicating matters, but there is a way around it:

tblRental will need an autoincrementing (probably pk) field on it. (I'll
call that RentID here)

Create a query based on tblRental. Make it a group query, just with CasID
and RentID on. Group by CasID with the max of RentID.

That will give you (assuming that RentID on your sample data is 1-6)
CasID,MaxOfRentID
1,6
2,5
3,3
4,4

Save that query and then create another one based on tblRental and the new
query. Link RentID and MaxOfRentID between the two tables and add a
criterion that Returned is N. You'll only need to output either of the
CasID fields. That will give you a list of IDs that have been rented, but
not returned from the latest rental.

Last step would be to just save that query and then create a subtract query
to exclude those ones from TblCasIDDetails.
 
Not going to work I'm afraid. Will include CasID 1 because it's been
returned in the past.
 
Back
Top