INNER JOIN UNIQUE

How are you defining "Unique records"?   Do you have true duplicates of records in tblStaff where ALL fields in the records are the same?

Apologies - let me clarify my question.  I only have single user records in tblStaff.  The problem I have is that users can be members of multiple groups so when the query runs I get user records repeated in the result set, based on the number of groups they are members of.

I'd like the result set to not repeat user records from tblStaff please.

Also to provide further clarification - this question is a progression of the question I asked here;

https://www.experts-exchange.com/questions/24900887/Inner-Join-SQL-query.html

Okay  - so your records in tblStaff look something like this:


User                     Group
-------------------------------
UserA                  Group1
UserA                  Group2
UserA                  Group3
UserB                  Group1
UserC                  Group1
UserC                  Group2
____________________


How do you decide which record is your distinct user record for use in your query?  Your resulting query is dependent on how that record is picked if you need "Group" information.

You need to define the criteria by which you will select a single record from tblStaffGroups for each tblStaff record.    

In other words, if there are more than one group record for a particular user,  do you want to pick one at random, the latest entered,  return some summary information in your query etc etc

Hello,

My records look like this;


tblStaff

User                        Name
-------------------------------
UserA                        Joe Blogs
UserB                        Sally Smith
UserC                        Anna Conda

tblGroups

User                        Name
-------------------------------
Group1                        Admins
Group2                        Editors
Group3                        Managers


tblStaffToGroups

User                     Group
-------------------------------
UserA                  Group1
UserA                  Group2
UserA                  Group3
UserB                  Group1
UserC                  Group1
UserC                  Group2

So for the example of;

SELECT * 
FROM tblStaffGroups g 
INNER JOIN tblStaffToGroups ug ON g.teGroupGUID = ug.teGroupDataGUID 
INNER JOIN tblStaff u ON ug.teGroupDataUser = u.teStGUID 
WHERE g.teGroupGUID <> 'Group1'

Select allOpen in new window

Id like the result set to be;


User                        Name
-------------------------------
UserA                        Joe Blogs
UserC                        Anna Conda

Rather than

User                        Name
-------------------------------
UserA                        Joe Blogs
UserA                        Joe Blogs
UserC                        Anna Conda

Understood - maybe.  The one table you havent defined in your last comment is tblStaffGroups (located in the FROM clause of your query).  Was this supposed to be tblStaff (see query below), or is it a completely seperate table?

Try this:

SELECT User, Name FROM tblStaff  
WHERE User IN (SELECT User FROM tblStaffToGroups) AND tblStaff.teGroupGUID  <> 'Group1'

Sorry the correct name of the table is tblStaffGroups not tblGroups

tblStaffGroups

User                        Name
-------------------------------
Group1                        Admins
Group2                        Editors
Group3                        Managers

Here is the corrected sample tables with all the correct field names and table names - so the query we are working on matches the sample data.

-- tblStaff


teStGUID                  teStFname
-----------------------------
User1                        John
User2                        Mary
User3                        Fred
User4                        Jane



-- tblStaffGroups

teGroupGUID                  teGroupTitle
----------------------------------
Group1                        Red
Group2                        Green
Group3                        Blue


-- tblStaffToGroups

teGroupDataUser            teGroupDataGUID
-----------------------------------
User1                        Group1
User1                        Group2
User1                        Group3
User2                        Group1
User2                        Group2
User3                        Group2

So for the example of;

SELECT * 
FROM tblStaffGroups g 
INNER JOIN tblStaffToGroups ug ON g.teGroupGUID = ug.teGroupDataGUID 
INNER JOIN tblStaff u ON ug.teGroupDataUser = u.teStGUID 
WHERE g.teGroupGUID <> 'Group1'

Select allOpen in new window

Id like the result set to be;


User                        Name
-------------------------------
User1                        John
User2                        Mary
User3                        Fred

Rather than

User                        Name
-------------------------------
User1                        John
User1                        John
User2                        Mary
User3                        Fred

That is (with the field name titles)

Id like the result set to be;


teStGUID                   teStFname
-------------------------------
User1                        John
User2                        Mary
User3                        Fred

Rather than

teStGUID                  teStFname
-------------------------------
User1                        John
User1                        John
User2                        Mary
User3                        Fred

Okay -

Try this:

SELECT User, Name FROM tblStaff  
WHERE User IN (SELECT User FROM tblStaffToGroups WHERE teGroupGUID  <> 'Group1')

Thank you very much.

Tried

SELECT User AS teStGUID , [Name] AS teStFname
FROM tblStaff  
WHERE User IN (SELECT User FROM tblStaffToGroups WHERE teGroupGUID  <> 'Group1')

Select allOpen in new window

Got the error

Msg 207, Level 16, State 1, Line 3
Invalid column name 'teGroupGUID'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Name'.

Select allOpen in new window

Modified it to;

SELECT User AS teStGUID , [Name] AS teStFname
FROM tblStaff  
WHERE User IN (SELECT User FROM tblStaffToGroups WHERE teGroupDataGUID  <> 'Group1')

Select allOpen in new window

Get the error

Msg 207, Level 16, State 1, Line 1
Invalid column name 'Name'.

Select allOpen in new window

Have just modified it to the following and it works as required;

SELECT *
FROM tblStaff  
WHERE User IN (SELECT User FROM tblStaffToGroups WHERE teGroupDataGUID  <> 'Group1')

Select allOpen in new window

Thank you very much.  I will post a new related question for another query I have.

Thanks for your patience in working through this to a solution.

Glad to help out :)