Problem with sql query

Question

Hello,

I'm using MySQL and I'm trying to construct a query to do the following:

I have:

 Table1 [ID,...]
 Table2 [ID, tID, start_date, end_date,...]

What I want from my query is:

Select all entires from Table2 Where Table1.ID=Table2.tID 
**where at least one** end_date<today.

The way I have it working right now is that if Table 2 contains (for example) 5 entries but only 1 of them is end_date< today then that's the only entry that will be returned, whereas I would like to have the other (expired) ones returned as well. I have the actual query and all the joins working well, I just can't figure out the ** part of it.

Any help would be great!

Thank you!

Answer 1

SELECT * FROM Table2
WHERE tID IN
  (SELECT Table2.tID FROM Table1
   INNER JOIN Table2 ON Table1.ID = Table2.tID
   WHERE Table2.end_date < NOW
  )

The subquery will select all tId's that match your where clause. The main query will use this subquery to filter the entries in table 2.

Note: the use of inner join will filter all rows from table 1 with no matching entry in table 2. This is no problem; these entries wouldn't have matched the where clause anyway.

Answer 2

Maybe, just maybe, you could create a sub-query to join with your actual tables and in this subquery you use a count() which can be used later on you where clause.