Left Outer Join is not working- returns duplicate company names?

westdh
westdh used Ask the Experts™
on
SELECT r.strName, u.strRegistrationUserName FROM i2Integration_EventRegv45_RegistrationUser u left outer join i2Integration_EventRegv45_Registration r on r.intRegistrationID = u.intRegistrationID

returns ??
-----------------------------------------------------------------------------------------------------
Petersen Construction Services      Perry Petersen
CALTROP Corporation                      Beth Power
Covello Group, Inc                                      David Jaworski
Harris & Associates                                      Dana LeSher
Harris & Associates                                      Ann Weaver
Harris & Associates                                      Tim Peel
Harris & Associates                                       Brad Chadwick
Kitchell CEM                                       Brian Bush
Holdrege & Kull                                       Tom Holdrege
Holdrege & Kull                                       Jeff Cox
Construction Testing Services                        Aaren McBride
Construction Testing Services                        Josh Romero
TRS Consultants Inc                                       Peter Owen
Vanir Construction Management, Inc.       Jeffrey Palumbo
4LEAF, Inc.                                       John Mahoney
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
seems like there are duplicate entries in one of those tables
Awarded 2008
Awarded 2008

Commented:
yes, your i2Integration_EventRegv45_Registration has dupes.
Awarded 2008
Awarded 2008

Commented:
maybe you're missing a field in your join?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Not true
---------------------------------
r.strName  -- thiers one row for each r.strName in table i2Integration_EventRegv45_Registration r

-----------------------------------
u.strRegistrationUserName -- thiers one row for each u.strRegistrationUserName in table i2Integration_EventRegv45_RegistrationUser u

thiers are duplicate ID
r.intRegistrationID = u.intRegistrationID

of which I am trying the outer join on
Awarded 2008
Awarded 2008

Commented:
but in 2Integration_EventRegv45_Registration, there are duplicate intRegistrationID  values...guaranteed.

Author

Commented:
Ok but how do I elimate duplicates in the r table

Harris & Associates                                      Dana LeSher
Harris & Associates                                      Ann Weaver
Harris & Associates                                      Tim Peel
Harris & Associates                                       Brad Chadwick

Harris & Associates                                      Dana LeSher
                                                                    Ann Weaver
                                                                    Tim Peel
                                                                     Brad Chadwick
Awarded 2008
Awarded 2008

Commented:
try this:

SELECT r.strName, u.strRegistrationUserName
FROM i2Integration_EventRegv45_RegistrationUser u
left outer join
      (
            SELECT intRegistrationID, strName = MAX(strName)
            FROM i2Integration_EventRegv45_Registration r
      ) r
      on r.intRegistrationID = u.intRegistrationID
Do you mean that from this:
Harris & Associates                                      Dana LeSher
Harris & Associates                                      Ann Weaver
Harris & Associates                                      Tim Peel
Harris & Associates                                       Brad Chadwick

you want this?

Harris & Associates                                      Dana LeSher
                                                                    Ann Weaver
                                                                    Tim Peel
                                                                     Brad Chadwick
If that's the case, what you're trying to do is not recommended at the database level. You should try doing that with a reporting tool or the application. It is really a bad practice and it will only bring you lots of headaches in the future. Having said that. Here's the code that can work for that. BUT AGAIN, it's really a bad practice.
 

;with CTE as (
	SELECT r.strName, u.strRegistrationUserName, row_number() over (partition by r.strName order by r.strName) rn
	FROM i2Integration_EventRegv45_RegistrationUser u 
	left outer join i2Integration_EventRegv45_Registration r on r.intRegistrationID = u.intRegistrationID 
)
select case when rn = 1 then strName else '' end as strName, strRegistrationUserName
from CTE

Open in new window

Commented:
Something like this:

SELECT r.strName, u.strRegistrationUserName FROM i2Integration_EventRegv45_RegistrationUser u left outer join (select Distinct * From i2Integration_EventRegv45_Registration)  r on r.intRegistrationID = u.intRegistrationID

Author

Commented:
;with CTE as (
        SELECT r.strName, u.strRegistrationUserName, row_number() over (partition by r.strName order by r.strName) rn
        FROM i2Integration_EventRegv45_RegistrationUser u  
        left outer join i2Integration_EventRegv45_Registration r on r.intRegistrationID = u.intRegistrationID  
)
select case when rn = 1 then strName else '' end as strName, strRegistrationUserName
from CTE
----------------------------------------------------------------------------------------------------
Yes this works great in managment studio express but not in my application

 coded like so..

Dim strSelectCommand As String = ";with CTE as (
        SELECT r.strName, u.strRegistrationUserName, row_number() over (partition by r.strName order by r.strName) rn
        FROM i2Integration_EventRegv45_RegistrationUser u  
        left outer join i2Integration_EventRegv45_Registration r on r.intRegistrationID = u.intRegistrationID
from CTE"

I am getting this error...

Compiler Error Message: BC30648: String constants must end with a double quote.

Source Error:

Line 76:         'Dim strSelectCommand As String = "SELECT r.intRegistrationID, e.dtmEvent,e.strTitle,r.strName, r.strEmail, r.fltOrderAmount, r.strRegistrationStatusID,u.strRegistrationUserName, r.dtmCreated FROM i2Integration_EventRegv45_Event e,i2Integration_EventRegv45_RegistrationUser u left outer join i2Integration_EventRegv45_Registration r on r.intRegistrationID = u.intRegistrationID where e.intEventID = r.intEventID and (YEAR(e.dtmEvent) = '" + ddlYear.SelectedValue + "' AND MONTH(e.dtmEvent) = '" + ddlMth.SelectedValue + "' AND e.strTitle = '" + DroplistData.SelectedValue.Tostring.replace("'","''") + "') and r.strPaymentMethodID = 'Check' and ((r.strRegistrationStatusID = 'pnd') OR (r.strRegistrationStatusID = 'cmp' )) Order by e.dtmEvent"
Line 77:        
Line 78: Dim strSelectCommand As String = ";with CTE as (
Line 79:         SELECT r.strName, u.strRegistrationUserName, row_number() over (partition by r.strName order by r.strName) rn
Line 80:         FROM i2Integration_EventRegv45_RegistrationUser u  
 



Awarded 2008
Awarded 2008

Commented:
does my query work?
you need to add some quotes there
Dim strSelectCommand As String = ";with CTE as ( 
        SELECT r.strName, u.strRegistrationUserName, row_number() over (partition by r.strName order by r.strName) rn 
        FROM i2Integration_EventRegv45_RegistrationUser u  
        left outer join i2Integration_EventRegv45_Registration r on r.intRegistrationID = u.intRegistrationID  
) 
select case when rn = 1 then strName else '''' end as strName, strRegistrationUserName 
from CTE"

Open in new window

Author

Commented:
No it did not work
Awarded 2008
Awarded 2008

Commented:
can you be more specific?

Author

Commented:
I did add qouted still not working in app

Dim strSelectCommand As String = ";with CTE as (  
        SELECT r.strName, u.strRegistrationUserName, row_number() over (partition by r.strName order by r.strName) rn  
        FROM i2Integration_EventRegv45_RegistrationUser u  
        left outer join i2Integration_EventRegv45_Registration r on r.intRegistrationID = u.intRegistrationID  
)  
select case when rn = 1 then strName else '''' end as strName, strRegistrationUserName  
from CTE"
ok, try like this:
Dim strSelectCommand As String = ";with CTE as (  
        SELECT r.strName, u.strRegistrationUserName, row_number() over (partition by r.strName order by r.strName) rn  
        FROM i2Integration_EventRegv45_RegistrationUser u   
        left outer join i2Integration_EventRegv45_Registration r on r.intRegistrationID = u.intRegistrationID   
)  
select case when rn = 1 then strName else chr(32) end as strName, strRegistrationUserName  
from CTE"

Open in new window

Author

Commented:

to chapmandew .. can you be more specific?

Column 'i2Integration_EventRegv45_Registration.intRegistrationID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

It's missing a group by there . But I think this is not what you're looking for.

SELECT r.strName, u.strRegistrationUserName
FROM i2Integration_EventRegv45_RegistrationUser u
left outer join
      (
            SELECT intRegistrationID, strName = MAX(strName)
            FROM i2Integration_EventRegv45_Registration r  
          group by intRegistrationID
      ) r
      on r.intRegistrationID = u.intRegistrationID
 

Author

Commented:
to ralmada

I try the new script and got the same error..

Compiler Error Message: BC30648: String constants must end with a double quote.

Source Error:

 

Line 76:         'Dim strSelectCommand As String = "SELECT r.intRegistrationID, e.dtmEvent,e.strTitle,r.strName, r.strEmail, r.fltOrderAmount, r.strRegistrationStatusID,u.strRegistrationUserName, r.dtmCreated FROM i2Integration_EventRegv45_Event e,i2Integration_EventRegv45_RegistrationUser u left outer join i2Integration_EventRegv45_Registration r on r.intRegistrationID = u.intRegistrationID where e.intEventID = r.intEventID and (YEAR(e.dtmEvent) = '" + ddlYear.SelectedValue + "' AND MONTH(e.dtmEvent) = '" + ddlMth.SelectedValue + "' AND e.strTitle = '" + DroplistData.SelectedValue.Tostring.replace("'","''") + "') and r.strPaymentMethodID = 'Check' and ((r.strRegistrationStatusID = 'pnd') OR (r.strRegistrationStatusID = 'cmp' )) Order by e.dtmEvent"
Line 77:        
Line 78: Dim strSelectCommand As String = ";with CTE as (  
Line 79:         SELECT r.strName, u.strRegistrationUserName, row_number() over (partition by r.strName order by r.strName) rn  
Line 80:         FROM i2Integration_EventRegv45_RegistrationUser u  
 

Source File: D:\Hosting\5374676\html\dotnetnuke\ReconSummaryRptCheck.aspx.vb    Line: 78
it seems to me that your error is not coming from the suggested query but from the one you're running before.

Author

Commented:
I ran the last  qoute in sql management studio and got this error

'chr' is not a recognized built-in function name.

Commented:
char
sorry, typo there. it should be char(32)

Author

Commented:
That work fine in the sudio manager but not in me app

the is my sub routine without the where clause and I still get the previous error

-----------------------------

Protected Sub ddlYear_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)

        Dim dtVolumeOrder As New DataTable()
               
Dim strSelectCommand As String = ";with CTE as (  
        SELECT r.strName, u.strRegistrationUserName, row_number() over (partition by r.strName order by r.strName) rn  
        FROM i2Integration_EventRegv45_RegistrationUser u    
        left outer join i2Integration_EventRegv45_Registration r on r.intRegistrationID = u.intRegistrationID    
)  
select case when rn = 1 then strName else char(32) end as strName, strRegistrationUserName  
from CTE"

         Using sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
            Using adapPatientBills As New SqlDataAdapter(strSelectCommand, sqlConn)
                adapPatientBills.Fill(dtVolumeOrder)
            End Using
        End Using
        'iRowsCount = dtVolumeOrder.Rows.Count - 1
        uxItemDetailGrid.DataSource = dtVolumeOrder
        uxItemDetailGrid.DataBind()

    End Sub
you mean this error?
Compiler Error Message: BC30648: String constants must end with a double quote.

See this other way

Dim strSelectCommand As String = "select case when rn = 1 then strName else char(32) end as strName, strRegistrationUserName   
from (   
        SELECT r.strName, u.strRegistrationUserName, row_number() over (partition by r.strName order by r.strName) rn   
        FROM i2Integration_EventRegv45_RegistrationUser u    
        left outer join i2Integration_EventRegv45_Registration r on r.intRegistrationID = u.intRegistrationID    
) a"

Open in new window

Author

Commented:
Another option....would you know how to make "" or NULL for duplicate names'

Like the followin sample

Registration
ID Paid by
Company
Name Paid by
Company
Email Fee
Amount Payment
Status Attendee
Name Date
Registered
Edit 27  KellcoMacs  ricki@kellcomacs.com  25.00  cmp  Ricki Reina  1/12/2010  
Edit 52  NULL  bonnieleekellogg@gmail.com  25.00  pnd  Sassy Kellogg  1/19/2010  
Edit 55  NULL  pat.christman@towill.com  25.00  pnd  Pat Christman  1/20/2010  
Edit 58  NULL  anilverma@earthlink.net  25.00  pnd  Vic Zikoor  1/21/2010  
Edit 66  NULL  cwornum@camsys.com  25.00  pnd  Christopher Wornum  1/21/2010  
Edit 82  NULL  christa@sensibleinc.net  50.00  pnd  Robert Sutton  1/22/2010  
Edit 82  NULL  christa@sensibleinc.net  50.00  pnd  Christa Johnson  1/22/2010  
Edit 86  NULL  baldwin@pgadesign.com  50.00  pnd  Maura Baldwin  1/22/2010  
Edit 86  NULL  baldwin@pgadesign.com  50.00  pnd  Cindy Angers  1/22/2010  

I am using this code
 Protected Sub uxItemDetailGrid_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs) Handles uxItemDetailGrid.RowDataBound
    If e.Row.RowType = DataControlRowType.DataRow Then
        Dim sValue As String = e.Row.Cells(2).Text
       
        If Values.Contains(sValue) Then
            ' This value is a duplicate . So displaying as NULL
            e.Row.Cells(2).Text = " NULL "
        Else
            Values.Add(sValue)
        End If
    End If
 End Sub

But is making all rows null after the first row.

Author

Commented:
to ralmada

that worked when I put it into the applicationcode and made the statement into one line...

Now I would like to add more fields into the select and a where clause where would i instert addition field and also where to put the where clause??
I'm going to use col1 and col2 as a sample, like this:
Dim strSelectCommand As String = "select case when rn = 1 then strName else char(32) end as strName, strRegistrationUserName, col1, col2    
from (    
        SELECT r.strName, u.strRegistrationUserName, row_number() over (partition by r.strName order by r.strName) rn, col1, col2    
        FROM i2Integration_EventRegv45_RegistrationUser u     
        left outer join i2Integration_EventRegv45_Registration r on r.intRegistrationID = u.intRegistrationID     
) a
where col2 = something"

Open in new window

Author

Commented:
Ok thank I add some columns and got this error

Exception Details: System.Data.SqlClient.SqlException: The multi-part identifier "e.dtmEvent" could not be bound.
The multi-part identifier "e.strTitle" could not be bound.
The multi-part identifier "e.intEventID" could not be bound.
The multi-part identifier "r.intEventID" could not be bound.
The multi-part identifier "e.dtmEvent" could not be bound.
The multi-part identifier "e.dtmEvent" could not be bound.
The multi-part identifier "e.strTitle" could not be bound.
The multi-part identifier "r.strPaymentMethodID" could not be bound.
The multi-part identifier "r.strRegistrationStatusID" could not be bound.
The multi-part identifier "r.strRegistrationStatusID" could not be bound.
The multi-part identifier "r.intRegistrationID" could not be bound.
The multi-part identifier "e.dtmEvent" could not be bound.
The multi-part identifier "e.strTitle" could not be bound.
The multi-part identifier "r.strEmail" could not be bound.
The multi-part identifier "r.fltOrderAmount" could not be bound.
The multi-part identifier "r.strRegistrationStatusID" could not be bound.
The multi-part identifier "r.dtmCreated" could not be bound.
The multi-part identifier "e.dtmEvent" could not be bound.

Source Error:


Line 82:         Using sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
Line 83:             Using adapPatientBills As New SqlDataAdapter(strSelectCommand, sqlConn)
Line 84:                 adapPatientBills.Fill(dtVolumeOrder)
Line 85:             End Using
Line 86:         End Using
 
try without the "e" alias there. or post your query.

Author

Commented:
here my query

Dim strSelectCommand As String = "select case when rn = 1 then strName else char(32) end as strName, strRegistrationUserName, r.intRegistrationID, r.strEmail, r.fltOrderAmount, r.strRegistrationStatusID, r.dtmCreated from ( SELECT r.strName, u.strRegistrationUserName, row_number() over (partition by r.strName order by r.strName) rn, r.intRegistrationID, r.strEmail, r.fltOrderAmount, r.strRegistrationStatusID, r.dtmCreated  FROM i2Integration_EventRegv45_Event e, i2Integration_EventRegv45_RegistrationUser u left outer join i2Integration_EventRegv45_Registration r on r.intRegistrationID = u.intRegistrationID) a where e.intEventID = r.intEventID and (YEAR(e.dtmEvent) = '" + ddlYear.SelectedValue + "' AND MONTH(e.dtmEvent) = '" + ddlMth.SelectedValue + "' AND strTitle = '" + DroplistData.SelectedValue.Tostring.replace("'","''") + "') and r.strPaymentMethodID = 'Check' and ((r.strRegistrationStatusID = 'pnd') OR (r.strRegistrationStatusID = 'cmp' ))"
try now:
 
Dim strSelectCommand As String = "select case when rn = 1 then strName else char(32) end as strName, strRegistrationUserName, intRegistrationID, strEmail, fltOrderAmount, strRegistrationStatusID, dtmCreated from ( SELECT r.strName, u.strRegistrationUserName, row_number() over (partition by r.strName order by r.strName) rn, r.intRegistrationID, r.strEmail, r.fltOrderAmount, r.strRegistrationStatusID, r.dtmCreated  FROM i2Integration_EventRegv45_Event e, i2Integration_EventRegv45_RegistrationUser u left outer join i2Integration_EventRegv45_Registration r on r.intRegistrationID = u.intRegistrationID where e.intEventID = r.intEventID and (YEAR(e.dtmEvent) = '" + ddlYear.SelectedValue + "' AND MONTH(e.dtmEvent) = '" + ddlMth.SelectedValue + "' AND strTitle = '" + DroplistData.SelectedValue.Tostring.replace("'","''") + "') and r.strPaymentMethodID = 'Check' and ((r.strRegistrationStatusID = 'pnd') OR (r.strRegistrationStatusID = 'cmp' ))) a"

Author

Commented:
That worked great thanks; Now if I want to cleanup other columms
like RegistartionID and Paid by Company Email

RegistrationID
Paid by Company Name
Paid by Company Email
Fee Amount
Payment Status
Attendee Name

Edit 58  Anil Verma Associates, Inc.  anilverma@earthlink.net  25.00  pnd  Vic Zikoor  
Edit 66  Cambridge Systematics, Inc.  cwornum@camsys.com  25.00  pnd  Christopher Wornum  
Edit 27  KellcoMacs  ricki@kellcomacs.com  25.00  cmp  Ricki Reina  
Edit 52  KELLCO-MACS  bonnieleekellogg@gmail.com  25.00  pnd  Sassy Kellogg  
Edit 55  Pat Christman  pat.christman@towill.com  25.00  pnd  Pat Christman  
Edit 86  PGAdesign  baldwin@pgadesign.com  50.00  pnd  Maura Baldwin  
Edit 86   baldwin@pgadesign.com  50.00  pnd  Cindy Angers  
Edit 82  Sensible Environmental Solutions Inc.  christa@sensibleinc.net  50.00  pnd  Robert Sutton  
Edit 82   christa@sensibleinc.net  50.00  pnd  Christa Johnson  
        325.00      
same idea
Dim strSelectCommand As String = "select case when rn = 1 then strName else char(32) end as strName, strRegistrationUserName, case when rn = 1 then cast(intRegistrationID as varchar) else char(32) end as intRegistrationID, case when rn = 1 then strEmail else char(32) end as strEmail, case when rn = 1 then cast(fltOrderAmount as varchar) else char(32) end as fltOrderAmount, case when rn = 1 then cast(strRegistrationStatusID as varchar) else char(32) end as strRegistrationStatusID, case when rn = 1 then cast(dtmCreated as varchar) else char(32) end as  dtmCreated from ( SELECT r.strName, u.strRegistrationUserName, row_number() over (partition by r.strName order by r.strName) rn, r.intRegistrationID, r.strEmail, r.fltOrderAmount, r.strRegistrationStatusID, r.dtmCreated  FROM i2Integration_EventRegv45_Event e, i2Integration_EventRegv45_RegistrationUser u left outer join i2Integration_EventRegv45_Registration r on r.intRegistrationID = u.intRegistrationID where e.intEventID = r.intEventID and (YEAR(e.dtmEvent) = '" + ddlYear.SelectedValue + "' AND MONTH(e.dtmEvent) = '" + ddlMth.SelectedValue + "' AND strTitle = '" + DroplistData.SelectedValue.Tostring.replace("'","''") + "') and r.strPaymentMethodID = 'Check' and ((r.strRegistrationStatusID = 'pnd') OR (r.strRegistrationStatusID = 'cmp' ))) a"  

Author

Commented:
Thank you very much, your where a big help...

Author

Commented:
I need additional help on the above answer: which I have working. The problem i have found is that it is working fine if the user enters a record with multiple 'intRegistrationCount' of 1 or even more than 1. the display is correct in that duplicate name are commented out. But if the user in time 'later' enters a record again with the same values, but at a later time I get a different intRegistrationID number and the code i am using still thinks it is part of the first record and comments out the fields. I need to have some way to deteremine that it is a new record and this would be the 'intRegistrationID' i need to treat this as a new record.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial