[RESOLVED] Not in sql

[cluelessPHP]

rental table
orderId(PK)
Userid(FK)
Locationid(FK)
startDate
exitDate

Location table
Locationid(PK)
locationPrice
OwnerId
Rooms
Pets
Garden
Garage
buildingType
maxOccupants
Discount
imageName
imageType
imagePath
Region

SELECT l.locationId
FROM location l 
WHERE l.loctionId NOT IN ( SELECT r.locationId FROM rentals r
    WHERE (r.exitDate BETWEEN '2016-10-01' AND '2016-10-30'
        OR r.startDate BETWEEN '2016-10-01' AND '2016-10-30')
) 

Based on those table tables I was trying to find the dates locations weren't rented but it keeps saying,

#1054 - Unknown column 'l.loctionId' in 'IN/ALL/ANY subquery', not sure if I'm on the right track or not

[NogDog]

You left out the "a" in "locationId"

[pbismad]

The error is telling the truth. Pay particular attention to the spelling of the column it is mentioning.

[cluelessPHP]

oops :o

SELECT l.locationId
FROM location l 
LEFT JOIN ( SELECT locationId 
FROM rentals 
WHERE startDate BETWEEN '2016-10-01' AND '2016-10-31') AS A ON l.locationId = A.locationId
WHERE A.locationId IS NULL

Maybe this way is better?

EDIT

Well both work now although slightly different ways

SELECT l.locationId, a.address, a.region, a.city, a.postcode
FROM location l 
LEFT JOIN address a
ON a.locationId=l.locationId
LEFT JOIN ( SELECT locationId 
FROM rentals 
WHERE startDate BETWEEN '2016-10-01' AND '2016-10-31') AS ren ON l.locationId = ren.locationId
WHERE ren.locationId IS NULL

Alternatively

SELECT l.locationId, a.address, a.region, a.city, a.postcode
FROM location l 
LEFT JOIN address a
ON a.locationId=l.locationId
WHERE l.locationId NOT IN ( SELECT r.locationId FROM rentals r
    WHERE (r.exitDate BETWEEN '2016-10-01' AND '2016-10-30'
        OR r.startDate BETWEEN '2016-10-01' AND '2016-10-30')
)