?
Solved

SQL Join not working properly

Posted on 2007-10-04
16
Medium Priority
?
186 Views
Last Modified: 2010-03-20
Hi, I have the following sql statment which works fine, but I have realised that the join has been done on the wrong field. When I change it to the correct field i now get 941 responses where there should only be 192.  What I require is to display for each row in the changes field the username from New_Users using changes.UserID as the match for New_Users.Member_ID to return the New_Users.Username.

Hope that all makes sense
Steve

** This stament works fine
SELECT     changes.Date, changes.Field, changes.ChangeFrom, changes.ChangeTo, New_Users.User_Name, changes.UserID
FROM         changes INNER JOIN New_Users ON changes.UserID = New_Users.User_ID
ORDER BY changes.Date

** This statement generates duplicated rows
SELECT     changes.Date, changes.Field, changes.ChangeFrom, changes.ChangeTo, New_Users.User_Name, changes.UserID
FROM         changes INNER JOIN
                      New_Users ON changes.UserID = New_Users.Member_ID
ORDER BY changes.Date
0
Comment
Question by:fusioninternet
  • 8
  • 4
  • 3
  • +1
16 Comments
 
LVL 14

Expert Comment

by:Jai S
ID: 20013127
u can use DISTINCT

SELECT    DISTINCT changes.Date, changes.Field, changes.ChangeFrom, changes.ChangeTo, New_Users.User_Name, changes.UserID
FROM         changes INNER JOIN
                      New_Users ON changes.UserID = New_Users.Member_ID
ORDER BY changes.Date

but tell whether the New_USers or changes will have duplicate MEMBER ID ?or both ?

0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20013255
This will give you one record per member id (the latest if I've got my sorting right):

    SELECT changes.Date, changes.Field, changes.ChangeFrom, changes.ChangeTo, New_Users.User_Name, changes.UserID
    FROM changes
    RIGHT OUTER JOIN New_Users ON changes.UserID = New_Users.Member_ID
    ORDER BY changes.Date DESC
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 20013292
try this

SELECT     changes.Date, changes.Field, changes.ChangeFrom, changes.ChangeTo, New_Users.User_Name, changes.UserID
FROM         changes ,                      New_Users
where
 changes.UserID = New_Users.Member_ID
ORDER BY changes.Date
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:fusioninternet
ID: 20013486
Hi jaiganeshsrinivasan,
I tried the above, but it didn't work, though it did remove 2 records. Please find below an example of the data. The changes table has multiple instances of the userid/memeber_ID

Example of changes data - Multliple Userid
Field            Change From      Change to                  Userid
Website            bbbbbbbbbbb      http://www.beifnoglou.gr            73
Phone Number      123456789            30 210 9431 0020            583
Emergency Telephone                  015602 500511            239
O-C Admin Email                  coops@tmd.com            239
O-C Admin Name                  coops                  239

Exampe of New_Users Data
      User_ID      User_Name                  Member_ID
      816      admin.503B                                              227
      31      sally.white                                             54
      599      sales                                                   172
      610      vinlio                                                  358
      803      cbalista                                                150
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20013544
it seems you need only one record from the NEW_USERS table...
you can do a LEFT OUTER JOIN...try this...
i have swapped the table names (changes,New_users)

SELECT     changes.Date, changes.Field, changes.ChangeFrom, changes.ChangeTo, New_Users.User_Name, changes.UserID
FROM         New_Users LEFT OUTER JOIN
                       changes ON changes.UserID = New_Users.Member_ID
ORDER BY changes.Date
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20013594
jaiganeshsrinivasan: That looks absolutely identical to my query with the tables swapped and then the join reversed to compensate.
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20013609
i do not say IDENTITCAL...but i ahve mentioned the swaping of table names and using LEFT OUTER JOIN...
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20013630
Yours:
    SELECT     changes.Date, changes.Field, changes.ChangeFrom, changes.ChangeTo, New_Users.User_Name, changes.UserID
    FROM         New_Users LEFT OUTER JOIN
                       changes ON changes.UserID = New_Users.Member_ID
    ORDER BY changes.Date

Yours with the same line breaks as mine:
    SELECT changes.Date, changes.Field, changes.ChangeFrom, changes.ChangeTo, New_Users.User_Name, changes.UserID
    FROM New_Users
    LEFT OUTER JOIN changes ON changes.UserID = New_Users.Member_ID
    ORDER BY changes.Date

Mine:
    SELECT changes.Date, changes.Field, changes.ChangeFrom, changes.ChangeTo, New_Users.User_Name, changes.UserID
    FROM changes
    RIGHT OUTER JOIN New_Users ON changes.UserID = New_Users.Member_ID
    ORDER BY changes.Date DESC
0
 
LVL 1

Author Comment

by:fusioninternet
ID: 20013645
Just to keep you updated. So far no go  with any of the statements.
Angelp1ay- your suggestion retuerns 1233 records
Pratima - your suggestion returns 491 record.
jaiganeshsrinivasan - your second suggestion returns 1233 records starting with a lot of null fields

0
 
LVL 14

Expert Comment

by:Jai S
ID: 20013673
angel...ca you leave these comparisons to the author and stop questioning me...
thnx...for yoyur information...all the SQL here are with the same line breaks...start comparing them...
0
 
LVL 1

Author Comment

by:fusioninternet
ID: 20013686
Also in case I wasn't being too clear the end result i would like from the example above is as follows:-

Field                              Change From         Change to                               Username
Website                         bbbbbbbbbbb        http://www.beifnoglou.gr      John SMith
Phone Number               123456789            30 210 9431 0020                  Fred Bloggs
Emergency Telephone                                 015602 500511                      George Formby
O-C Admin Email                                          coops@tmd.com                    George Formby
O-C Admin Name                                         coops                                     George Formby
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20013735
hi
SELECT      changes.Date, changes.Field, changes.ChangeFrom,
            changes.ChangeTo, (select top 1 from new_users nu where changes.UserID = New_Users.User_ID) as username,
            changes.UserID
FROM      changes
ORDER BY changes.Date

this will return you the proper user name...
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20013744
SELECT      changes.Date, changes.Field, changes.ChangeFrom,
            changes.ChangeTo, (select top 1 from new_users nu where changes.UserID = nu.User_ID) as username,
            changes.UserID
FROM      changes
ORDER BY changes.Date
0
 
LVL 1

Author Comment

by:fusioninternet
ID: 20013790
HI, Sorry i tried both from above and get the error :-
Missing or incomplete SELECT clause. (incorrect syntax near the keyword  'FROM')

0
 
LVL 14

Accepted Solution

by:
Jai S earned 2000 total points
ID: 20013802
yeah sorry
SELECT      changes.Field, changes.ChangesFrom,
            changes.ChangesTo,
            (select top 1 username from new_users nu where changes.UserID = nu.memberid) as username,
            changes.UserID
FROM      changes

and this my structure
create table changes(field varchar(100),ChangesFrom varchar(100),changesTo varchar(100),userid int)
create table New_Users(userid int,username varchar(100),memberid int)

0
 
LVL 14

Expert Comment

by:Jai S
ID: 20013809
just add the fields that you want in the select clause...i removed a few (lazy for creating the exact table struct as yours)...
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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
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

840 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