Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2008
    Posts
    28

    Question Unanswered: Problem grabbing correct data on a join

    Hi folks,

    I'm a little new to SQL programming but I'm learning.

    I need to do a join on a table where I exclude records that have more than one urn (unique record number).

    In the example table below I want to exclude both entries in the cr_urn column of 49074 & 49075.

    cr_urn proc_urn crs_seq crp_seq crp_site

    49073 2333 1 1 NULL
    49074 2051 1 1 NULL
    49074 2615 1 2 NULL
    49075 1280 1 1 NULL
    49075 2185 1 2 1945
    49076 2333 1 1 NULL
    49077 2333 1 1 NULL
    49078 1145 1 2 1875

    To get to this ->

    cr_urn proc_urn crs_seq crp_seq crp_site

    49073 2333 1 1 NULL
    49076 2333 1 1 NULL
    49077 2333 1 1 NULL
    49078 1145 1 2 1875

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    try this..


    select cr_urn proc_urn crs_seq crp_seq crp_site

    from table
    inner join
    (
    select cr_urn, count(proc_urn) as urncount
    from table
    group by cr_urn
    having count(proc_urn) = 1

    ) a

    on table.cr_urn = a.cr_urn
    Cheers....

    baburajv

  3. #3
    Join Date
    Mar 2008
    Posts
    28
    baburajv, I think you almost have it but there is a small problem. I don't necessarily want the data from that table only two columns so I can join it to another.

    ie.

    SELECT
    cr.cr_urn,
    cr.book_urn,
    op.proc_mnc

    FROM
    case_record cr (NOLOCK)

    INNER JOIN (select cr_urn, count(proc_urn) as urncount, proc_urn
    from case_record_procedure
    group by cr_urn, proc_urn
    having count(proc_urn) = 1) crp (NOLOCK) ON crp.cr_urn=cr.cr_urn

    INNER JOIN orprocedure op (NOLOCK) ON op.proc_urn=crp.proc_urn

    As is this returns me the following error -
    'crp' has more columns than were specified in the column list.

  4. #4
    Join Date
    Mar 2008
    Posts
    28
    Was able to work around the problem by first putting the results in a temporary table. However, I'm still trying to figure out what was wrong with the derived table.

Posting Permissions

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