INNER JOIN UNIQUE

Hello,

How can I modify this query so I am only selecting unique records from tblStaff table?


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 <> 'GROUP-ABC-12-34-56' AND teStArchived = 'No' AND teStVerified='Yes'

Open in new window

sonic1234Asked:
Who is Participating?
 
mbizupCommented:
Using the resulting field name you posted in your last comment:


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


0
 
mbizupCommented:
How are you defining "Unique records"?   Do you have true duplicates of records in tblStaff where ALL fields in the records are the same?
0
 
sonic1234Author Commented:
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.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_24900887.html
0
 
mbizupCommented:
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.
0
 
Paul_Harris_FusionCommented:
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
0
 
sonic1234Author Commented:
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'

Open 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
0
 
mbizupCommented:
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'



0
 
sonic1234Author Commented:
Sorry the correct name of the table is tblStaffGroups not tblGroups

tblStaffGroups

User                        Name
-------------------------------
Group1                        Admins
Group2                        Editors
Group3                        Managers
0
 
sonic1234Author Commented:
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
0
 
sonic1234Author Commented:
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'

Open 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
0
 
sonic1234Author Commented:
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
0
 
mbizupCommented:
Okay -

Try this:

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



0
 
sonic1234Author Commented:
Thank you very much.

Tried

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

Open 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'.

Open 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')

Open in new window


Get the error

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

Open 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')

Open in new window


Thank you very much.  I will post a new related question for another query I have.
0
 
sonic1234Author Commented:
Thanks for your patience in working through this to a solution.
0
 
mbizupCommented:
Glad to help out :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.