I’m stuck with the following problem:
I have a table (‘dog_status’) that looks like this:
dog_id person_id status date_from date_to reserved_for prov_month
1 0 adopted 19/12/11
2 1 adopted 31/03/16
3 0 adopted 30/11/07
10 1 adopted 10/03/06 11/10/17
13 1 fostered 06/11/17 0
14 0 adopted 07/11/17
14 1 fostered 06/11/17 07/11/17 0 Nov
16 3 adopted 21/06/15
I’ve constructed a query (reproduced below) so that the ‘id’ columns are decode via their respective parent tables (‘dog’ & ‘person’), so that the out put is more meaningful as it will subsequently to be used as the basis for a form. The ‘reserved_for’ column also links back to the ‘person’ table (‘person_id’).
SELECT RIGHT( 100000 + CAST( “ds”.“dog_id” AS VARCHAR ( 4 ) ), 4 ) || ’ ’ || “d”.“name” AS “Dog”,
RIGHT( 100000 + CAST( “ds”.“person_id” AS VARCHAR ( 4 ) ), 4 ) || ’ ’ || “p”.“surname” || ’ ’ || “p”.“1st_name” AS “Person”,
“ds”.“status” AS “Status”,
“ds”.“date_from” AS “Date From”,
“ds”.“date_to” AS “Date To”,
( SELECT CASE WHEN “ds”.“reserved_for” != “ds”.“person_id”
AND “ds”.“reserved_for” IS NOT NULL
THEN RIGHT( 100000 + CAST( “ds”.“reserved_for” AS VARCHAR ( 4 ) ), 4 ) || ’ ’ || “p1”.“surname” || ’ ’ || “p1”.“1st_name”
ELSE ‘Test’ END
FROM “dog_status” “ds”
JOIN “person” “p1” ON “ds”.“reserved_for” = “p1”.“person_id”
WHERE “ds”.“dog_id” = “d”.“dog_id” ) AS “Reserved For”,
“ds”.“prev_month” AS “Prov Dep Date”
FROM “dog_status” “ds”
JOIN “dog” “d” ON “ds”.“dog_id” = “d”.“dog_id”
JOIN “person” “p” ON “ds”.“person_id” = “p”.“person_id”
WHERE “d”.“name” = :Dog_Name
ORDER BY “ds”.“date_from”
My problem is that where there is an entry in column ‘reserved_for’ and where there exists more that 1 row, as in ‘dog_id’ 14, the decoded person details appear in both rows. I’ve phaffed around with this for several days without any joy. If anyone can give me a clue, it would be very much appreciated.