I want to only get those ids where there are 2 rows and one row has the site_id = 1, the other site_id = 2.
I tried joining the table on twice, but the query took too long. So what I am now doing is concatenating the site ids, and restricting the rows to only those where 1 is in this concat value, and 28 is in it. However, I can't get it to work. It is to do with site_id being a number and my concat values i.e. sites being a string? How can I make sure that both these values are in my concatenated string?
SELECT *, COUNT(id) as num_ids
FROM (
SELECT id, CONVERT(GROUP_CONCAT(`site_id`) USING utf8) as `sites`, MAX(`Date_Added`) as `date_added_lb`
FROM `product_location` pl
WHERE `site_id` =1 OR `site_id` = 28
GROUP BY id
) as t
WHERE t.`date_added_lb` >="2013-02-27"
AND 1 IN(`sites`)
AND 28 IN(`sites`)
AND num_ids=2
By testing in the HAVING clause that the COUNT(DISTINCT site_id) = 2 when the WHERE clause has already filtered down to site_id IN (1,28), you can validate it without the subquery.
SELECT
id
FROM production_location
WHERE
date_added_lb >= '2013-02-27'
/* Filters for only site_id 1, 28 */
AND site_id IN (1, 28)
GROUP BY id
/* And verifies that the group has exactly 2 different results (one of each 1, 28) */
HAVING COUNT(DISTINCT site_id) = 2
Update:
To verify that only the site_id = 28 has date_added_bl >= '2013-02-27', you need a slightly smarter subquery:
WHERE
site_id = 1
OR (site_id = 28 AND date_added_lb >= '2013-02-27')
Nothing else needs to change.
If performance doesn't seem to meet your need, verify that there is an index on site_id, and one on id. If possible, it may benefit from a composite index on (id, site_id).