-2

I have the tables below

Table GroupChat

GroupChatId int
GroupCode varchar(20)
Name varchar(50)

Table GCMembers

ID int 
ParticipantUserID int
GroupChatID int

Table Chats

ID
Sender
Receiver

I run the following query

Select distinct GC.GroupChatID, GC.Name
from Chats chats 
inner join GroupChats GC on chats.Receiver = GC.GroupCode
inner join GCMembers GCM on GC.GroupChatID = GCM.GroupChatID 
where GCM.ParticipantUserID = 3

This works perfectly but I want to only have distinct GroupChatID. In other words if GroupChatID already exists in the result of the query don't bother adding it. Value GCM.ParticipantUserID comes from another table (foreign key) that doesn't have to do anything with this question so I didn't include that here. Any int value could work.

How can I achieve that? Thanks for your help in advance.

Sample Data

GroupChat
GroupChatID - GroupCode - Name
    1             GC1    Group1
    2             GC2     Group2

--

Chats
ID - SENDER - RECIEVER
 1      2        GC1
 2      3        GC2
 3      3        GC1
 4      3        GC1

GCMembers
ID - ParticipantUserID - GroupChatID
1           2                1
2          3                1
3           3                2

The output should be

GroupchatID:1 and Name:Group1
aaarianme
  • 282
  • 4
  • 17
  • I would help if you showed exactly what output you expected with some sample data – Martin Cairney Feb 18 '21 at 05:13
  • I have added more information let me know if anything is unclear – aaarianme Feb 18 '21 at 06:11
  • With that data you won't get any results - Chats.Receiver is an integer but GroupChats.GroupCode is GC1, GC2 etc - these will never have matching values to JOIN on – Martin Cairney Feb 18 '21 at 06:27
  • the foreign key for that `GroupChatID` – aaarianme Feb 18 '21 at 06:31
  • Your query included ```from Chats chats inner join GroupChats GC on chats.Receiver = GC.GroupCode``` So, as I said the data types and values between ```chats.Receiver``` and ```GC.GroupCode``` do not match and no rows will return. Either your query or sample data is wrong – Martin Cairney Feb 18 '21 at 06:43
  • I was trying to simplify the data sample without all the other unnecessarily columns but it didnt workout. It should be good now – aaarianme Feb 18 '21 at 06:49
  • You filter ```GCMembers``` by ```ParticipantUserID = 3```. Looking at the sample data that would still provide GroupChatID values of 1 AND 2. Your data doesn't filter down to just 1 row. Do you just need the first row or do you expect to receive all the rows matching this filter? – Martin Cairney Feb 18 '21 at 07:09
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/228878/discussion-between-iamaaarianme-and-martin-cairney). – aaarianme Feb 18 '21 at 07:33
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – SMor Feb 18 '21 at 12:44

2 Answers2

0

What you are looking for is OVER(PARTITION BY...).

Unfortunately, I don't have an SQL server running at the moment, and I don't want to write code untested, but you can find examples here: https://www.sqlshack.com/sql-partition-by-clause-overview/

Segev -CJ- Shmueli
  • 1,535
  • 14
  • 15
0

You need to group the rows, and select any value for Name. SQL does not have an ANY function, but you can use MIN or MAX:

Select GC.GroupChatID, MIN(GC.Name) AS Name
 from Chats chats inner join GroupChats GC on chats.Reciever=GC.GroupCode
inner join GCMembers GCM on GC.GroupChatID=GCM.GroupChatID 
where GCM.ParticipantUserID=3
GROUP BY GC.GroupChatID
Charlieface
  • 52,284
  • 6
  • 19
  • 43