Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Base, query combining records to one

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.

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"

Base, query combining records to one

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.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"