CODEWITHSUNDEEP
mysqlleft-joininner-join
user1256477
user1256477

Reputation: 11201

left join or similar query

I have a renting schema in my database.

It has 3 tables, user, rentingTrack and lock.

user has as unique element RFIDCode. in rentingTrack I may have the user(he or she rented at least once) or not (he/she never rented). If the user rented I need the returnLock for the last rent, as rentingID is autoincremented, it is max(rentingID). With lock table I got the parkingID, the data I need.

enter image description here

The problem is how to get the parkingID, I have half query:

 SELECT  u.userID, l.parkingID

 FROM locks l, user u

LEFT JOIN (SELECT r1.* FROM rentingTrack r1 INNER JOIN 
(SELECT userID, MAX(rentingID) maxRentingID FROM rentingTrack GROUP BY userID) r2 
on r1.rentingID = r2.maxRentingID) r on u.userID = r.userID 

 WHERE u.userCodeRFID= 3166681355

What i need is, if userID isn't in the rentingTrack table I got:

   userID   |  parkingID
  ----------+-----------
        34  |     NULL

And if userID is in the rentingTrack

   userID   |  parkingID
  ----------+-----------
        34  |        5

how can I complete the query to get parkingID?

Upvotes: 0

Views: 44

Answers (2)

Barmar
Barmar

Reputation: 782489

SELECT  u.userID, l.parkingID

FROM user u

LEFT JOIN (SELECT r1.userID, r1.returnLock
           FROM rentingTrack r1
           INNER JOIN 
              (SELECT userID, MAX(rentingID) maxRentingID
               FROM rentingTrack
               GROUP BY userID) r2 
           ON r1.rentingID = r2.maxRentingID) r on u.userID = r.userID 

LEFT JOIN locks l ON r.returnLock = l.lockID

WHERE u.userCodeRFID= 3166681355

Upvotes: 0

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

Try This. (Need some sample data for duplicates)

select distinct u.userID,l.parkingID
from users
left join rentingTrack r on u.userID=r.userID
left join locks l on r.returnLock=l.lockid

Upvotes: 1

Related Questions