Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: Newbie help with joins

    I am learning vb.net and ms sql (2000 evaluation version); I can't get my joins to work in Tsql:

    select m.lname,m.fname,m.idno,m.payorno,p.payorno.p.payor name
    from dbtemp..d_mbrdata m join dbtemp..d_payors p
    on (m.payorno = p.payorno)

    inner join query returns no records at all. left outerjoin returns the mbrdata fields, but the payors fields show as null. payorno in both tables is type integer and both tables definitely have matching records. I created index for both tables on payorno.

    Does anyone have an idea what i am doing wrong here? Thanks.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Newbie help with joins

    There is nothing wrong with query. If left join returns some data - problem is in second table (matching records).

    Just run this query (without join):

    select *
    from dbtemp..d_mbrdata
    where payorno in (select distinct payorno from dbtemp..d_payors)

  3. #3
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    Yeah, from the way u'r describing u'r results, it seems there is no m.payorno that is equal to p.payorno.


    Maybe u can try debuggin further by inserting a new record into both this tables with a "confirm" identical payorno and do your select join statement again.
    It should return 1 joined record.
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try running this and see what key values are returned:
    select m.payorno, p.payorno
    from dbtemp..d_mbrdata m
    full outer join dbtemp..d_payors p on (m.payorno = p.payorno)
    where mpayrono is null or p.payorno is null

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •