CODEWITHSUNDEEP
mysqlsql
Sterling Duchess
Sterling Duchess

Reputation: 2080

MySQL Exclude results from JOIN

I have two tables: 

locations
---------------------
| id    INT, PK, AI |
| x     INT         |
| y     INT         |
---------------------

signals
---------------------
| id    INT, PK, AI |
| location_id INT   |
| mac         INT   |
| strength    INT   |
---------------------

One location can (will) have up to maximum of 4 signals. Where location is X,Y point on an image and signal is Access Point in range of X,Y and it's signal strength.

Anyhow I have a method to which I provide a List of up to 4 MAC addresses and I need to find all locations that contain those 4 addresses.

Right now I'm doing this programmatically by:
1. Take top signal strength at X,Y and it's mac addresses
2. SELECT * FROM signals WHERE mac = ScanResult.BSSID
3. Create array of ID's from returned signals.location_id
4. In bulk select all locations if their ID is in the array along with all signals related to those locations
5. Complex loop in loop creates an array containing all locations that have in their relationship all the 4 mac addresses that I provided in the List and delete others.

This is incredibly messy and has redundant queries but since I'm not very good with SQL it was a patch that sort of worked.

Now I was wondering if I can do this using SQL alone and return locations that contain those 4 mac addresses.

I have: 

SELECT locations.*, signals.* FROM locations INNER JOIN signals ON locations.id = signals.location_id;

I would be less confused if I only had to exclude locations where a relation would be 1:1 but here each locations has up to 4 signals. Is there a way I could provide an "array" to the query and say from that JOIN remove all locations that do not contain this number of mac addresses and these mac addresses.

Upvotes: 1

Views: 141

Answers (3)

Andomar
Andomar

Reputation: 238078

You can use a having clause to ensure a location has all four MAC addresses:

SELECT  l.id
,       l.x
,       l.y
FROM    locations l
JOIN    signals s
ON      s.location_id = l.location_id
WHERE   s.MAC in (1, 2, 3, 4)
GROUP BY
        l.id
,       l.x
,       l.y
HAVING  COUNT(DISTINCT s.MAC) = 4

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Andomar's solution is correct based on the sample query. However, the data structure differs from the query, and the query doesn't really make sense. I think this is all that is necessary:

SELECT s.location_id
FROM signals s
WHERE s.MAC in (1, 2, 3, 4)
GROUP BY s.location_id
HAVING COUNT(DISTINCT s.MAC) = 4;

Upvotes: 1

Rod
Rod

Reputation: 804

Is there a way I could provide an "array" to the query

Yes, there is an operator for this. You can even provide another query as parameter

SELECT * FROM locations WHERE id IN (SELECT location_id FROM signals WHERE mac = ScanResult.level);
  • you can also use NOT IN to exclude elements which are contained in the list

Upvotes: 2

Related Questions