CODEWITHSUNDEEP
sqlms-access
Forrest
Forrest

Reputation: 31

Select distinct values in same column by group

Using Microsoft Access and having a hard time figuring out how to get the desired results. I have two linked tables with an inner join on [bed] with the following data in both tables. I am looking to select [room number] that has a [gender] mismatch (has more than one distinct value per room number). I have searched stackoverflow and haven't found anything that seems to both group AND select distinct by group [room number].

Table 1
-----------------
Room Number | Bed

101 | 101a
101 | 101b
101 | 101c
102 | 102a
102 | 102b
103 | 103a
103 | 103b


Table 2
-----------------
Bed | Gender

101a | Male
101b | Male
101c | Female
102a | Male
102b | Male
103a | Female
103b | Undefined

With this data set, I would expect it to return Room 101 and 103 with the associated genders.

SQL Query

SELECT ROOM_NO FROM RMGT_T_ROOMS INNER JOIN RMGT_T_ROOM_CONFIGS ON RMGT_T_ROOMS.PK_BED_SPACE = RMGT_T_ROOM_CONFIGS.CK_BED_SPACE GROUP BY RMGT_T_ROOMS.FK_ROOM_NO HAVING COUNT(DISTINCT GENDER) > 1

Upvotes: 3

Views: 6215

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

How about inner join and group by?

select t1.room
from t1 inner join
     t2
     on t1.bed = t2.bed
group by t1.room
having min(gender) <> max(gender);

If you know there are two genders only, you can add min(gender) and max(gender) to the select.

Another method uses exists:

select t1.room, t2.gender
from t1 inner join
     t2
     on t1.bed = t2.bed
where exists (select 1
              from t1 as t11 inner join
                   t2 as t22
                   on t11.bed = t22.bed
              where t22.gender <> t2.gender
             );

Upvotes: 2

Mureinik
Mureinik

Reputation: 311163

You could join that table on the bed's id and count the distinct number of genders:

SELECT   room_number
FROM     t1
JOIN     t2 ON t1.bed = t2.bed
GROUP BY room_number
HAVING   COUNT(DISTINCT gender) > 1

Upvotes: 2

Related Questions