Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2007

    Post Unanswered: Removing duplicate rows from query, a little help please...

    Hi all,

    I have a query that produces duplicate rows.

    It is a more complex query, but the join involved is on just two tables.

    The main table
    Impact table

    Main table has a row for each record.
    Each record can have 0 to 2 types of impacts, which are held in the Impact Table, joined to the main table on = impact.main_id

    When I run this query against a record that has both a Client and Internal I get two lines.
    I am using the following select to extract two columns.

    CASE WHEN dbo.tbl_IPM_IncidentImpact.Impact_Type = 1 THEN 'Yes' ELSE 'No' END as 'Client Impact',
    CASE WHEN dbo.tbl_IPM_IncidentImpact.Impact_Type = 2 THEN 'Yes' ELSE 'No' END as 'Internal Impact'
    But I get two rows, one saying Yes for Client and No for Internal and VV

    The join is here.

    	dbo.tbl_IPM_IncidentImpact on -- To get the Impact Types
    	dbo.tbl_IPM_IncidentMain.IM_ID = dbo.tbl_IPM_IncidentImpact.Impact_IncidentID
    Is there anyway of getting the values into the two fields, but on the same line.



  2. #2
    Join Date
    Feb 2004
    One Flump in One Place
    Wrap your CASE expressions in a MAX() aggregate function and group on the rest of the columns

  3. #3
    Join Date
    Jul 2007
    Thanks for the reply, this works on it's own in a query, but not in the more complex one.

    Maybe it's to do with the remainder of the joins. I'll test next week.

Posting Permissions

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