Problem with sql query

Question

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!

Scharrels · Accepted Answer · 2010-08-06 20:45:05Z

3

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.

answered Aug 6, 2010 at 20:45

Scharrels

3,06526 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MilkyWayJoe · Accepted Answer · 2010-08-06 20:46:58Z

0

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.

answered Aug 6, 2010 at 20:46

MilkyWayJoe

9,1122 gold badges41 silver badges53 bronze badges

Collectives™ on Stack Overflow

Problem with sql query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related