CODEWITHSUNDEEP
mysqlsql
quinn123
quinn123

Reputation: 3

Perform conditional operation on records having same keys

I have a table which looks like this -

City             MobilePlan
Scranton         Unlimited1
Seattle          Limited2
Scranton         Unlimited8
Olympia          Limited4
Olympia          Unlimited4
Albany           Limited1
Sacramento       Unlimited3
Seattle          Limited2
Albany           Unlimitd2

Now, I need to find the cities which only offer limited plans. I tried using the below query:

Select * from table_name where MobilePlan like 'Unlimited%'

but it obviously doesn't work. What kind of query is needed here?

Upvotes: 0

Views: 37

Answers (3)

nbk
nbk

Reputation: 49373

You had it almost right, what you need i a subquery that is not in city.

The Distinct is there, because you have for Seattle 2 Limited Rows and so it shows only once in the result. 

CREATE TABLE Table1
    (`City` varchar(10), `MobilePlan` varchar(10))
;

INSERT INTO Table1
    (`City`, `MobilePlan`)
VALUES
    ('Scranton', 'Unlimited1'),
    ('Seattle', 'Limited2'),
    ('Scranton', 'Unlimited8'),
    ('Olympia', 'Limited4'),
    ('Olympia', 'Unlimited4'),
    ('Albany', 'Limited1'),
    ('Sacramento', 'Unlimited3'),
    ('Seattle', 'Limited2'),
    ('Albany', 'Unlimitd2')
;

✓

✓

SELECT DISTINCT `City`  
FROM Table1 
WHERE  `MobilePlan` LIKE 'Limited%'
AND `City` NOT IN (SELECT `City` FROM Table1 WHERE `MobilePlan` LIKE 'Unlimited%')

| City    |
| :------ |
| Seattle |
| Albany  |

db<>fiddle here

Upvotes: 0

mickmackusa
mickmackusa

Reputation: 47864

You don't need a subquery. Group on the city, and use the HAVING clause to express your qualifying criteria.

The logic demands at least one Limited% row and zero Unlimited% rows for a respective City.

SELECT `City`
FROM `Table1`
GROUP BY `City`
HAVING IF(MAX(`MobilePlan` LIKE 'Limited%'), 1, 0) = 1
   AND IF(MAX(`MobilePlan` LIKE 'Unlimited%'), 1, 0) = 0

Result set:

Seattle
Albany

Perhaps less intuitive, but more concise:

SELECT `City`
FROM `Table1`
GROUP BY `City`
HAVING MAX(`MobilePlan` LIKE 'Limited%') = 1
   AND MAX(`MobilePlan` LIKE 'Unlimited%') = 0

Upvotes: 0

Nick
Nick

Reputation: 147146

You can use a NOT EXISTS query to find cities that have a limited plan but no unlimited plans:

SELECT DISTINCT City
FROM plans p1
WHERE MobilePlan LIKE 'Limited%'
  AND NOT EXISTS (SELECT *
                  FROM plans p2
                  WHERE p2.City = p1.City AND p2.MobilePlan LIKE 'Unlimited%')

Output (for your sample data)

City
Seattle
Albany

Demo on SQLFiddle

Note that your sample data has a typo in the last entry for Albany, it has Unlimitd2 instead of Unlimited2. If that row is changed, the output becomes

City
Seattle

Demo on SQLFiddle

Upvotes: 1

Related Questions