Post reply

Join Problems - Multiple/Identical Results

  • December 13, 2002 at 7:35 am

    #105979

    I encountered an issue with using either left outer join, join ... on two (2) tables that I used. The REGCOMPANY in the example stores company profile (one record per company). The REGATTENDEE stores all attendees for a certain company.

    If I join the two tables with the REGATTENDEE as the left table, as in the following code, the result set are two (2) rows, which is contrary to what I currently have in the database. The expected result set (based on querying the two tables separately), should be just ONE row.

    /*** --------------- ***/

    select *

    from regattendee ra join regcompany rc on ra.companykey=rc.companykey

    where ra.showno='0184' and rc.companyclass='BUYER'

    and rtrim(rc.country)='UNITED STATES' and ra.badgestatus='N'

    and ra.contactid='MG00240158'

    order by rc.companyname, ra.contactlname, ra.contactfname

    /*** --------------- ***/

    You may suggest using 'distinct' with the select statement, which I did

    with the following code:

    /*** --------------- ***/

    select distinct(ra.contactid), rc.companyname

    from regattendee ra join regcompany rc on ra.companykey=rc.companykey

    where ra.contactid='MG00240158'

    /*** --------------- ***/

    The result set is just one row. BUT the issue remains on the first code.

    The first code meets all the requirements of properly written code BUT there seems to be problem with using JOIN on the two tables.

    Any solution or tips will be greatly appreciated ... may be you have encountered this seeming contradiction before ...

    Thanks

  • December 13, 2002 at 8:09 am

    #444350

    What do the following queries return?

    select *

    from regattendee ra

    where

    ra.showno='0184'

    and ra.badgestatus='N'

    and ra.contactid='MG00240158'

    select *

    from regcompany rc

    where

    rc.companyclass='BUYER'

    and rtrim(rc.country)='UNITED STATES'

    Regards,

    Andy Jones

    .

  • December 13, 2002 at 10:52 am

    #444374

    Thanks for the response ... I can't figure where you are leading at but

    here are the results using the count(*)

    -----------

    1

    (1 row(s) affected)

    -----------

    15452

    (1 row(s) affected)

    thanks ...

  • December 13, 2002 at 5:53 pm

    #444401

    it's ok ... i got the solution which i will post next week

    thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply