Can't Remove Duplicate Rows

Question

Each record could have up to 40 different person_user.description fields. Problem is, I am getting duplicate rows because records have multiple description entries. Can you help me put those additional duplicates on the same record row like:

|1|badge.bid|person.first_name|person.last_name|person.type|1|2|3|4|5|etc|40| |2|badge.bid|person.first_name|person.last_name|person.type|1|2|3|4|5|etc|40| |3|badge.bid|person.first_name|person.last_name|person.type|1|2|3|4|5|etc|40|

instead of this:

|1|badge.bid|person.first_name|person.last_name|person.type|person_user.description| |1|badge.bid|person.first_name|person.last_name|person.type|person_user.description| |2|badge.bid|person.first_name|person.last_name|person.type|person_user.description| |2|badge.bid|person.first_name|person.last_name|person.type|person_user.description|

SELECT person.id, 
       badge.bid, 
       person.first_name, 
       person.last_name, 
       person.type, 
       person_user.description 
FROM   person, 
       badge, 
       person_user 
WHERE  person.id = badge.id 
       AND person.id = person_user.person_id 
       AND badge.bid NOT LIKE "111%" 
       AND badge.access_date >= 20130401 
GROUP  BY person.id, 
          badge.bid, 
          person.first_name, 
          person.last_name, 
          person.type, 
          person_user.description 
ORDER  BY person.id

score 1 · Answer 1 · answered Jul 11 '13 at 20:33

Got it figured out. Here is how I achieved it by the way.

SELECT P.id,
       B.bid,
       P.first_name,
       P.last_name,
       PT.description,
       PU1.description PU1,
       PU2.description PU2,
       PU3.description PU3,
       PU4.description PU4,
       PU5.description PU5,
       PU6.description PU6,
       PU7.description PU7,
       PU8.description PU8,
       PU9.description PU9,
       PU10.description PU10,
       PU11.description PU11,
       PU12.description PU12,
       PU13.description PU13,
       PU14.description PU14,
       PU15.description PU15,
       PU16.description PU16,
       PU17.description PU17,
       PU18.description PU18,
       PU19.description PU19,
       PU20.description PU20,
       PU21.description PU21,
       PU22.description PU22,
       PU23.description PU23,
       PU24.description PU24,
       PU25.description PU25,
       PU26.description PU26,
       PU27.description PU27,
       PU28.description PU28,
       PU29.description PU29,
       PU30.description PU30,
       PU31.description PU31,
       PU32.description PU32,
       PU33.description PU33,
       PU34.description PU34,
       PU35.description PU35,
       PU36.description PU36,
       PU37.description PU37,
       PU38.description PU38,
       PU39.description PU39,
       PU40.description PU40
FROM person P,
     OUTER badge B,
           person_type PT,
           OUTER person_user PU1,
                 OUTER person_user PU2,
                       OUTER person_user PU3,
                             OUTER person_user PU4,
                                   OUTER person_user PU5,
                                         OUTER person_user PU6,
                                               OUTER person_user PU7,
                                                     OUTER person_user PU8,
                                                           OUTER person_user PU9,
                                                                 OUTER person_user PU10,
                                                                       OUTER person_user PU11,
                                                                             OUTER person_user PU12,
                                                                                   OUTER person_user PU13,
                                                                                         OUTER person_user PU14,
                                                                                               OUTER person_user PU15,
                                                                                                     OUTER person_user PU16,
                                                                                                           OUTER person_user PU17,
                                                                                                                 OUTER person_user PU18,
                                                                                                                       OUTER person_user PU19,
                                                                                                                             OUTER person_user PU20,
                                                                                                                                   OUTER person_user PU21,
                                                                                                                                         OUTER person_user PU22,
                                                                                                                                               OUTER person_user PU23,
                                                                                                                                                     OUTER person_user PU24,
                                                                                                                                                           OUTER person_user PU25,
                                                                                                                                                                 OUTER person_user PU26,
                                                                                                                                                                       OUTER person_user PU27,
                                                                                                                                                                             OUTER person_user PU28,
                                                                                                                                                                                   OUTER person_user PU29,
                                                                                                                                                                                         OUTER person_user PU30,
                                                                                                                                                                                               OUTER person_user PU31,
                                                                                                                                                                                                     OUTER person_user PU32,
OUTER person_user PU33,
      OUTER person_user PU34,
            OUTER person_user PU35,
                  OUTER person_user PU36,
                        OUTER person_user PU37,
                              OUTER person_user PU38,
                                    OUTER person_user PU39,
                                          OUTER person_user PU40
WHERE P.id=B.person_id
AND P.type=PT.id
AND (P.id=PU1.person_id
     AND PU1.slot_number=1)
AND (P.id=PU2.person_id
     AND PU2.slot_number=2)
AND (P.id=PU3.person_id
     AND PU3.slot_number=3)
AND (P.id=PU4.person_id
     AND PU4.slot_number=4)
AND (P.id=PU5.person_id
     AND PU5.slot_number=5)
AND (P.id=PU6.person_id
     AND PU6.slot_number=6)
AND (P.id=PU7.person_id
     AND PU7.slot_number=7)
AND (P.id=PU8.person_id
     AND PU8.slot_number=8)
AND (P.id=PU9.person_id
     AND PU9.slot_number=9)
AND (P.id=PU10.person_id
     AND PU10.slot_number=10)
AND (P.id=PU11.person_id
     AND PU11.slot_number=11)
AND (P.id=PU12.person_id
     AND PU12.slot_number=12)
AND (P.id=PU13.person_id
     AND PU13.slot_number=13)
AND (P.id=PU14.person_id
     AND PU14.slot_number=14)
AND (P.id=PU15.person_id
     AND PU15.slot_number=15)
AND (P.id=PU16.person_id
     AND PU16.slot_number=16)
AND (P.id=PU17.person_id
     AND PU17.slot_number=17)
AND (P.id=PU18.person_id
     AND PU18.slot_number=18)
AND (P.id=PU19.person_id
     AND PU19.slot_number=19)
AND (P.id=PU20.person_id
     AND PU20.slot_number=20)
AND (P.id=PU21.person_id
     AND PU21.slot_number=21)
AND (P.id=PU22.person_id
     AND PU22.slot_number=22)
AND (P.id=PU23.person_id
     AND PU23.slot_number=23)
AND (P.id=PU24.person_id
     AND PU24.slot_number=24)
AND (P.id=PU25.person_id
     AND PU25.slot_number=25)
AND (P.id=PU26.person_id
     AND PU26.slot_number=26)
AND (P.id=PU27.person_id
     AND PU27.slot_number=27)
AND (P.id=PU28.person_id
     AND PU28.slot_number=28)
AND (P.id=PU29.person_id
     AND PU29.slot_number=29)
AND (P.id=PU30.person_id
     AND PU30.slot_number=30)
AND (P.id=PU31.person_id
     AND PU31.slot_number=31)
AND (P.id=PU32.person_id
     AND PU32.slot_number=32)
AND (P.id=PU33.person_id
     AND PU33.slot_number=33)
AND (P.id=PU34.person_id
     AND PU34.slot_number=34)
AND (P.id=PU35.person_id
     AND PU35.slot_number=35)
AND (P.id=PU36.person_id
     AND PU36.slot_number=36)
AND (P.id=PU37.person_id
     AND PU37.slot_number=37)
AND (P.id=PU38.person_id
     AND PU38.slot_number=38)
AND (P.id=PU39.person_id
     AND PU39.slot_number=39)
AND (P.id=PU40.person_id
     AND PU40.slot_number=40)
AND B.status=0
AND B.bid NOT LIKE "111%"
AND B.access_date>=20130401
ORDER BY P.id,
         P.last_name

The SQL is ugly as sin — for all it works! I wouldn't indent the OUTER clauses like that; they're all at the same level. I'd put each join condition onto one line, too. And I'd probably use explicit LEFT JOIN instead of the Informix-style (non-standard, old-fashioned) OUTER joins. I'm also not convinced I'd have gathered that this was your requirement from the question. — Jonathan Leffler, Jan 20 '15 at 05:24

score 0 · Answer 2 · answered Jul 09 '13 at 00:15

0

You can use the listagg() function to get rid of your multiple rows problem; however, it will not put each description in separate columns as you have described in your question. Instead it will put all the descriptions in a single column as string separated by the character(s) you specify. The example below will separate the descriptions by a comma and space:

SELECT person.id, 
       badge.bid, 
       person.first_name, 
       person.last_name, 
       person.type, 
       listagg(person_user.description, ', ') within group (order by person_user.description)
FROM   person, 
       badge, 
       person_user 
WHERE  person.id = badge.id 
       AND person.id = person_user.person_id 
       AND badge.bid NOT LIKE "111%" 
       AND badge.access_date >= 20130401 
GROUP  BY person.id, 
          badge.bid, 
          person.first_name, 
          person.last_name, 
          person.type
ORDER  BY person.id

answered Jul 09 '13 at 00:15

Hue

1

I tried that command, but it was by recognized unfortunately. – John Brooks Jul 09 '13 at 07:09
What version of SQL are you using? – Hue Jul 09 '13 at 15:50
It is Informix, as the question tags clearly indicate. And Informix does not have a LISTAGG function built-in. It's possible to add a [GROUP_CONCAT](http://stackoverflow.com/questions/715350/show-a-one-to-many-relationship-as-2-columns-1-unique-row-id-comma-separate/716655#716655) aggregate to Informix. It isn't clear that it's what's wanted though. Rather than 1 column with up to 40 sub-fields, the desired output seems to be 40 separate columns. – Jonathan Leffler Jan 20 '15 at 05:22

score 0 · Answer 3 · edited May 23 '17 at 12:21

0

To achieve that, in Informix you need to define your own aggregate function following these instructions.

edited May 23 '17 at 12:21

Community

1
1

answered Jul 10 '13 at 07:46

Javide

2,477
5
45
61

Can't Remove Duplicate Rows

3 Answers3

Related