Querying partial values, but not identical

Databases
1

I’m attempting to get the list of personid’s which has two different TYPE%'s (without it being the same type).

See the table below, personid 100 would satisfy because of TYPE1 and TYPE2, but personid 200 would not because of only having TYPE1 twice.


columnid | personid | type
1        | 100      | TYPE1
2        | 100      | TYPE2
3        | 100      | TYPE1
4        | 200      | HELLO
5        | 200      | TYPE1
6        | 200      | TYPE1
7        | 101      | WORLD

I’m working towards using the GROUP BY statement, this obviously catches the personid’s with the same TYPE as mentioned above. Is there any way to add a distinct of some kind so that doesn’t happen?


SELECT typetable.personid
FROM typetable
WHERE typetable.type like 'TYPE%'
GROUP BY typetable.personid
HAVING count(*) > 1
2

Nevermind, using a WHERE IN seems to work…

WHERE typetable.type in ('TYPE1','TYPE2')

Just thought there might have been a way without specifically defining each of the types.

3 

select personid
  from typetable
 where type like 'TYPE%'
 group by personid
having count(distinct type) > 1
4

Excellent, thank you! I was trying to put the DISTINCT inside the SELECT.

5

Just out of interest, whenever I create a JOIN to the person table it expands the results for some reason (so it’s no longer accurate). Is there a problem joining a table while using a GROUP BY like this?


SELECT typetable.personid
FROM typetable
JOIN person on person.personid = typetable.personid
WHERE typetable.type like 'TYPE%'
GROUP BY typetable.personid
HAVING count(distinct type) > 1

Disregard this… :slight_smile: