Sub Select & Inner Join

Databases
1

Guys I am struggling with a sub select in an inner join. Here is what I have

SELECT
  v.* , a.area
FROM
  villas v
INNER JOIN
  areas a ON v.area = a.id
INNER JOIN
  (SELECT COUNT( DISTINCT timestamp) FROM villa_dates WHERE timestamp BETWEEN '1241326800' AND '1243746000') <> '0' villa_dates vd
ON
  vd.villa_id = v.id
WHERE
  primary_image <> '0'
LIMIT 0 , 30

Essentially I would like to search for villas that are available within the timestamps. Which means that villas will be returned with if the count = 0.

2

Try


SELECT
    v.* 
  , a.area
FROM villas v
INNER JOIN areas a 
ON v.area = a.id
LEFT OUTER JOIN
  (SELECT 
     villa_id
   FROM villa_dates 
   WHERE TIMESTAMP BETWEEN '1241326800' AND '1243746000'
  ) AS  vd
ON vd.villa_id = v.id
WHERE primary_image <> '0'
AND   vd.villa_id IS NULL
LIMIT 0 , 30

Btw, I don’t understand this line:

WHERE TIMESTAMP BETWEEN ‘1241326800’ AND ‘1243746000’

Is TIMESTAMP the name of a column of the villa_dates table?

3

Hi Guido,

Thanks for the response, yes timestamp is the name of the column in the villa_dates table. I have now changed it to d_timestamp to not confuse things.

This is what the query is looking like

SELECT
  v.*, a.area
FROM
  villas v
INNER JOIN
  areas a
ON
  v.area = a.id
LEFT OUTER JOIN (SELECT DISTINCT villa_id FROM villa_dates WHERE d_timestamp NOT BETWEEN '1241931600' AND '1242018000')
AS
  vd
ON
  vd.villa_id = v.id

Unfortunately it is still not working, and returning all results. I want it to only return results with no entries in the villa_dates table. I have added the NOT as well.

Would there perhaps be a way of doing a count so all villas are returned with a total of how many entries are in villa_dates between the two timestamps?

Thanks for the help again.

4

try it like this –

SELECT v.*
     , a.area
  FROM villas AS v
INNER
  JOIN areas AS a
    ON a.id = v.area
LEFT OUTER
  JOIN (
       SELECT villa_id
         FROM villa_dates
        WHERE d_timestamp [COLOR="Blue"]BETWEEN 1241931600 AND 1242018000[/COLOR]
       ) AS vd
    ON vd.villa_id = v.id
 [COLOR="blue"]WHERE vd.villa_id IS NULL[/COLOR]

you might not understand what the IS NULL is doing in the WHERE clause, but it’s basically intended to return only those villas which do not have a row in the villa_dates table with the specified d_timestamp

this is the way a LEFT OUTER JOIN works – you specify what you’re looking for in the ON clause, and then you use IS NULL to return only those rows from the left table which had no match in the right table

5

You are a dude, thanks very much that is working.

Cheers

6

Of course it returns all results, you eliminated this part of the query I posted:
AND vd.villa_id IS NULL
:nono:

Fortunately, r937 explained to you what it means, and why it is needed :smiley:

7

Sorry guido2004, I wasn’t being ignorant.

Thanks for the help guys, much appreciated.