0

I have four tables, assuming they have only an id as a column each.

  • listings
  • feature
  • location
  • l_f_location as a junction table with FKs on listingId, featureId, locationId

I try to query the l_f_location table for a given set of locationIds (logical AND match) and want as a result only those listings which match ALL the locationIds, e.g. (7 AND 9 AND 10).

l_f_location looks like

listingId featureId locationId
10        5         7
10        7         7
10        8         9
11        4         7
11        8         9
11        9         10
11        12        14

The goal is to retrieve only listingId 11 in this case - matching the set of locationIds 7, 9 and 10.

I tried the following query

"SELECT id, COUNT(*) as num FROM l_f_locations WHERE locationId IN ( 7, 9, 10) GROUP by listingId, locationId HAVING num = 3"

But that gives wrong values of count as the grouping kicks in.

A similar query works perfectly on a simpler junction table e.g. only l_location with e.g. "SELECT id, COUNT(*) as num FROM l_location WHERE locationId IN ( 7, 9, 10) GROUP by listingId HAVING num = 3".

Rgds, P.

PMiller
  • 241
  • 1
  • 5
  • 15
  • in the first query why are you including `locationId` in the `GROUP BY` clause? – John Woo Sep 25 '13 at 12:08
  • I group by listingId and locationId to reduce the results to what I would like to count per listingId. – PMiller Sep 25 '13 at 12:10
  • No, I am looking for a listingId which has 7,8,9. It might have other, but that is fine in this query. – PMiller Sep 25 '13 at 12:13
  • Added another line in the l_f_location table - so listingId 11 would still match, has having 7,9,10. – PMiller Sep 25 '13 at 12:14
  • that's what i'm confused of, you have already a working query but you want to add `locationID` in the group by clause which i don't understand. sorry – John Woo Sep 25 '13 at 12:15
  • No, I do not have working query. I have one for another, more simple junction table, e.g. l_location (no featureId FK). – PMiller Sep 25 '13 at 12:17

1 Answers1

3

I'm assuming that the same listingId and locationId can have multiple featureIds? Otherwise it's quite a bit easier.

If that is the case:

SELECT listingId, COUNT(*) as num 
FROM
(SELECT DISTINCT listingId, locationId
 FROM l_f_locations
 WHERE locationId IN ( 7, 9, 10)) AS sub
GROUP by listingId, HAVING num = 3
hcarver
  • 7,126
  • 4
  • 41
  • 67
  • Yes, you assumed correctly. The inner select needs to be aliased, so i tworks like `SELECT listingId, COUNT(*) as num FROM (SELECT DISTINCT listingId, locationId FROM l_f_locations WHERE locationId IN ( 7, 9, 10)) AS foo GROUP by listingId, HAVING num = 3` – PMiller Sep 25 '13 at 12:27
  • Great. Let me know whether it works. If not, it might be easiest to create a SQL Fiddle demonstrating the problem. – hcarver Sep 25 '13 at 12:29
  • I'm testing as I speak, it looks good so far. Will do some more testing and then accept your answer. Many thanks! – PMiller Sep 25 '13 at 12:31
  • Does it fail without an `AS` clause? I think it should still work. – hcarver Sep 25 '13 at 14:39
  • Yes it fails with #1248 - Every derived table must have its own alias. – PMiller Sep 25 '13 at 15:31
  • 1
    Thanks for letting me know. I've updated that answer with that. – hcarver Sep 25 '13 at 15:38