CODEWITHSUNDEEP
sqldb2
齐天大圣
齐天大圣

Reputation: 1189

SQL select rows with same id are all having the same value

I got a table like this:

+--------+------+-----+
|Provider|Type  |Date |
+--------+------+-----+
|1       |Meal  |Date1|
+--------+------+-----+
|1       |ACCOM |Date2|
+--------+------+-----+
|2       |Meal  |Date3|
+--------+------+-----+
|2       |Meal  |Date4|
+--------+------+-----+
|3       |ACCOM |Date5|
+--------+------+-----+
|3       |ACCOM |Date6|
+--------+------+-----+

A provider can provide either accommodation or meal on a date. Is there a way i can select all providers who only provide meal or only provide accommodation?

So i am expecting:

+--------+------+-----+
|2       |Meal  |Date3|
+--------+------+-----+
|2       |Meal  |Date4|
+--------+------+-----+
|3       |ACCOM |Date5|
+--------+------+-----+
|3       |ACCOM |Date6|
+--------+------+-----+

Since provider 2 only provides meal and provider 3 only provides accommodation.

UPDATE:

The original question is:

List all providers who only provide accommodations or meals (not both).

NOTE: do not eliminate duplicates in the result.

So I think i should get duplicates in one-step instead of joining table again.

I came up with the following query:

SELECT PROVIDERS.PROVID AS ID
FROM PROVIDERS
INNER JOIN ISP ON ISP.PROVID = PROVIDERS.PROVID
WHERE ISP.SVCTYPE = 'ACCOM'

UNION ALL

SELECT PROVIDERS.PROVID AS ID
FROM PROVIDERS
INNER JOIN ISP ON ISP.PROVID = PROVIDERS.PROVID
WHERE ISP.SVCTYPE = 'MEAL'

The problem is this query has duplicated code, the only difference is 'ACCOM' or 'MEAL' so can this query be improved again?

Upvotes: 0

Views: 2302

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

Here is a way where you get only one row per provider:

select provider, min(type)
from t
group by provider
having min(type) = max(type);

If you actually want the duplicates then use join:

select t.*
from t join
     (select provider, min(type)
      from t
      group by provider
      having min(type) = max(type)
     ) tt
     on t.provider = p.provider;

Upvotes: 2

Related Questions