Only select records when they have multiple records

Question

I was wondering if it's possible to select some records from the database that have at least 3 records matching the query?

For example. I currently have a course which has 20 different locations and all have 3 dates:

Course 1: location Amsterdam dates: 01-09-2013 - 03-09-2013 - 02-10-2013

Course 2: location Amsterdam dates: 10-10-2013 - 11-10-2013 - 12-10-2013

I have the dates saved seperately in the database. Now when I select the dates of today and higher I only get the last date of course 1 (the first of course 2). That isn't what I want.

I want it to check whether the first two dates also match the statement and then it can select the first date. So in this example I only want the second course to be selected.

This is the query I have:

$query = "SELECT locaties_datum.id, locaties_datum.cursus_id, 
locaties_datum.locatie_id, locaties_datum.van, locaties_datum.code, 
locaties.plaats, locaties.link FROM locaties, locaties_datum WHERE 1 = 1 AND 
locaties_datum.locatie_id = locaties.locatie_id AND 
locaties_datum.van > '". strtotime("now") ."' AND 
locaties.active = '1' AND 
locaties_datum.cursus_id = '9' AND 
locaties.locatie_id = '71' 
GROUP BY locaties_datum.code 
ORDER BY locaties_datum.van ASC ";

Is it possible to do what I want?

Why are you saving the dates individually? What is the purpose of the three dates? Are the classes a three day class and those are the three days or is the same class being held three different times? — wesleywmd, Sep 19 '13 at 15:32

score 0 · Answer 1 · answered Sep 19 '13 at 16:03

This is probably what you want:

SELECT d.id, d.cursus_id, d.locatie_id, d.van, d.code, l.plaats, l.link
FROM locaties AS l
INNER JOIN locaties_datum AS d ON d.locatie_id = l.locatie_id
WHERE l.active = '1'
AND d.cursus_id = '9'
AND l.locatie_id = '71'
AND EXISTS (SELECT 1 FROM locaties_datum AS sd WHERE sd.locatie_id = d.locatie_id AND sd.van > ?)
ORDER BY d.van ASC

This will select all locaties and all their locaties_datum if the locaties_datum has at least one row whose van is greater than the parameter supplied (e.g. TODAY).

As a side note: You should either pass the date as a parameter to the query or use the sql-server's builtin function. Don't interpolate it into the query-string. Also, your ints are strings ?

Thank you for your reply. When I use the query you supplied. I get an error:`You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?) ORDER BY d.van ASC' ` How do I pass the date to the query? — user2485647, Sep 20 '13 at 06:16
See this: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — user2722968, Sep 20 '13 at 09:45

Only select records when they have multiple records

1 Answers1