CODEWITHSUNDEEP
mysqlsql
Khairu Aqsara
Khairu Aqsara

Reputation: 1310

find related postcode that just belongs to specific offices

halo, I've two table offices_postcodes and offices

offices_postcodes = id,postcode,office_id
offices   = id,offices,department

the case is: one office has many postcodes, and one postcode belongs to many offices, Example:

postcode 0100036 has relation with office A
postcode 0100036 has relation with office B
postcode 0100035 only has relation with office A
postcode 0100037 has relation with office A
postcode 0100037 has relation with office B
postcode 0100039 only has relation with office A

I want to find all postcode that belongs to office A, but not belongs to Office B, in this case is 0100035 and 0100039.

can we do that ?, here is what i've done so far, 

SELECT Count(`offices_postcodes`.postcode), 
       `offices_postcodes`.postcode 
FROM   `offices_postcodes`, 
       `offices` 
WHERE  `offices_postcodes`.office_id = `offices`.id 
       AND `offices`.department_id = 1 
       AND offices_postcodes.deleted_at IS NULL 
GROUP  BY `offices_postcodes`.postcode 
HAVING Count(`offices_postcodes`.postcode) = 1

Data postcodes:

id  postcode    office_id
1   0100036     271 
2   0100036     275 
3   0100035     271 
4   0100037     271
5   0100037     275 
6   0100039     271

Data Offices

id      offices     department_id
271     A           1
275     B           1

Expected Result

postcode 
0100035
0100039

Upvotes: 0

Views: 31

Answers (1)

D-Shih
D-Shih

Reputation: 46239

I think you can try to use JOIN and connect condition on op.office_id = o.id

Schema (MySQL v5.7)

CREATE TABLE offices_postcodes(
   id INT,
   postcode VARCHAR(50),
   office_id INT
);




INSERT INTO offices_postcodes VALUES (1,'0100036',271); 
INSERT INTO offices_postcodes VALUES (2,'0100036',275); 
INSERT INTO offices_postcodes VALUES (3,'0100035',271); 
INSERT INTO offices_postcodes VALUES (4,'0100037',271);
INSERT INTO offices_postcodes VALUES (5,'0100037',275); 
INSERT INTO offices_postcodes VALUES (6,'0100039',271); 

CREATE TABLE offices(
   id INT,
   postcode VARCHAR(50),
   department_id INT
);


INSERT INTO offices VALUES (271,'A',1);
INSERT INTO offices VALUES (275,'B',1);

Query #1

SELECT  op.postcode 
FROM `offices_postcodes` op JOIN `offices` o
ON op.office_id = o.id 
GROUP BY op.postcode 
having  Count(op.postcode) = 1;

| postcode |
| -------- |
| 0100035  |
| 0100039  |

View on DB Fiddle

Upvotes: 1

Related Questions