SQL Join not working properly

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
LVL 1
fusioninternetAsked:
Who is Participating?
 
Jai SConnect With a Mentor Tech ArchCommented:
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
 
Jai STech ArchCommented:
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
 
Angelp1ayCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Pratima PharandeCommented:
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
 
fusioninternetAuthor Commented:
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
 
Jai STech ArchCommented:
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
 
Angelp1ayCommented:
jaiganeshsrinivasan: That looks absolutely identical to my query with the tables swapped and then the join reversed to compensate.
0
 
Jai STech ArchCommented:
i do not say IDENTITCAL...but i ahve mentioned the swaping of table names and using LEFT OUTER JOIN...
0
 
Angelp1ayCommented:
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
 
fusioninternetAuthor Commented:
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
 
Jai STech ArchCommented:
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
 
fusioninternetAuthor Commented:
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
 
Jai STech ArchCommented:
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
 
Jai STech ArchCommented:
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
 
fusioninternetAuthor Commented:
HI, Sorry i tried both from above and get the error :-
Missing or incomplete SELECT clause. (incorrect syntax near the keyword  'FROM')

0
 
Jai STech ArchCommented:
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
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.