In a Base Query, I am trying to combine several phone number records into one line for each member ID, up to four . I created a View that contains the phone numbers and an added sequence number (e.g. ID #1 has 3 phone numbers, sequenced 1, 2 and 3). My Query does combine the phone numbers, but it also repeats the combined line for each record in the View. Can’t see the cause. The View (which works) and query are below. Any help pointing out my error will be greatly appreciated.
SELECT "PhoneID", "ItemNr", "PhoneNumber", CASEWHEN( "Cell" = 1, 'C', '' ) "Cell" FROM "PhoneNumbers" AS "b" LEFT JOIN ( SELECT "g"."PhoneID", COUNT( * ) "ItemNr", "g"."PhoneName" FROM "PhoneNumbers" AS "g" LEFT JOIN "PhoneNumbers" AS "o" ON "g"."PhoneID" = "o"."PhoneID" AND "g"."PhoneName" >= "o"."PhoneName" GROUP BY "g"."PhoneID", "g"."PhoneName" ) AS "z" ON "b"."PhoneID" = "z"."PhoneID" AND "b"."PhoneName" = "z"."PhoneName" ORDER BY "PhoneID" ASC, "PhoneName" ASC;
SELECT "PhoneID", ( SELECT "PhoneNumber" || ' ' || "Cell" FROM "View_Group" WHERE "PhoneID" = "a"."PhoneID" AND "ItemNr" = 1 ) || IFNULL( ( SELECT ', ' || "PhoneNumber" || ' ' || "Cell" FROM "View_Group" WHERE "PhoneID" = "a"."PhoneID" AND "ItemNr" = 2 ), '' ) || IFNULL( ( SELECT ', ' || "PhoneNumber" || ' ' || "Cell" FROM "View_Group" WHERE "PhoneID" = "a"."PhoneID" AND "ItemNr" = 3 ), '' ) || IFNULL( ( SELECT ', ' || "PhoneNumber" || ' ' || "Cell" FROM "View_Group" WHERE "PhoneID" = "a"."PhoneID" AND "ItemNr" = 4 ), '' ) "PhoneNumbers" FROM "View_Group" AS "a"