CODEWITHSUNDEEP
mysql
user1016265
user1016265

Reputation: 2397

Mysql interesting query

I have the next table: 

+---------+------------+
| firm_id | service_id |
+---------+------------+
|       6 |          2 |
|       6 |          4 |
|      23 |          7 |
|      23 |          6 |

I want to get ONLY companies who do not have service_id=4 in their service list.

A query of the above table should return only the company with firm_id=23 because firm_id=6 has one record with service_id=4.

I want to make it with one query. Is this possible (without joins)?

Thanks.

P.S. Thanks everyone. User "derobert" suggested very interesting way, what i was looking for.

Upvotes: 1

Views: 293

Answers (3)

derobert
derobert

Reputation: 51157

You can do it several ways. Here is one, with a correlated subquery:

SELECT DISTINCT firm_id FROM table t1
  WHERE NOT EXISTS ( SELECT 1 FROM table t2 WHERE t1.firm_id = t2.firm_id AND t2.service_id = 4)

In MySQL-land it is often better to rewrite as a self-join:

SELECT DISTINCT firm_id
  FROM table t1 LEFT JOIN table t2 ON (t1.firm_id = t2.firm_id AND t2.service_id = 4)
  WHERE t2.firm_id IS NULL

Finally, here is one way to do it that doesn't involve subqueries or joins (but I expect performs worse than either of the above)

SELECT firm_id, CONCAT(',', GROUP_CONCAT(service_id SEPARATOR ','), ',') AS service_ids
  FROM table t1
  GROUP BY firm_id
  HAVING service_ids NOT LIKE '%,4,%'

I confess I haven't actually run these; please forgive typos.

Upvotes: 5

Marco
Marco

Reputation: 57583

You could try

SELECT * FROM your_table
WHERE firm_id NOT IN
  (SELECT DISTINCT firm_id FROM your_table
   WHERE service_id = 4) a

As suggested by ypercube (thanks!) you can also try

SELECT DISTINCT firm_id, service_id  FROM your_table
WHERE firm_id NOT IN
  (SELECT firm_id FROM your_table
   WHERE service_id = 4) a

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115590

SELECT DISTINCT
    firm_id
FROM
    TableX AS t 
WHERE
    NOT EXISTS
      ( SELECT 
           *
        FROM 
           TableX AS s
        WHERE 
           s.firm_id = t.firm_id
        AND 
           s.service_id = 4
      )

or:

SELECT 
    firm_id
FROM
    TableX 
GROUP BY 
    firm_id
HAVING
    COUNT(service_id = 4) = 0

Upvotes: 3

Related Questions