Ask Your Question
0

Base, query combining records to one [closed]

asked 2017-10-30 16:34:30 +0100

Joe Castor gravatar image

updated 2017-10-30 16:35:29 +0100

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"
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Joe Castor
close date 2017-10-30 18:22:10.025601

1 Answer

Sort by » oldest newest most voted
0

answered 2017-10-30 17:52:39 +0100

Ratslinger gravatar image

Hello,

This should work. In your second statement (SELECT from View_Group) start the statement as SELECT DISTINCT "PhoneID", etc.......

edit flag offensive delete link more

Comments

Hello to you back, and once again, thank you. It works Perfect. Thank you again very much.

Joe Castor gravatar imageJoe Castor ( 2017-10-30 18:17:51 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2017-10-30 16:34:30 +0100

Seen: 29 times

Last updated: Oct 30 '17