?
Solved

INNER JOIN UNIQUE

Posted on 2011-10-30
16
Medium Priority
?
357 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:sonic1234
  • 9
  • 6
16 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37054501
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
 

Author Comment

by:sonic1234
ID: 37054528
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
 

Author Comment

by:sonic1234
ID: 37054538
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 61

Expert Comment

by:mbizup
ID: 37054588
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
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 37054599
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
 

Author Comment

by:sonic1234
ID: 37054630
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37054673
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
 

Author Comment

by:sonic1234
ID: 37054702
Sorry the correct name of the table is tblStaffGroups not tblGroups

tblStaffGroups

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

Author Comment

by:sonic1234
ID: 37054734
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
 

Author Comment

by:sonic1234
ID: 37054744
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
 

Author Comment

by:sonic1234
ID: 37054749
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37054756
Okay -

Try this:

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



0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 37054760
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
 

Author Comment

by:sonic1234
ID: 37054822
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
 

Author Closing Comment

by:sonic1234
ID: 37054823
Thanks for your patience in working through this to a solution.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37056393
Glad to help out :)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question