select subquery for child table

Question

I am having some trouble with the following issue, will show you what I have, what I want and what I have tried...

What I want: I have a parent and child table, the parent is

visits

and the child table is

assessments

, there is a one to many relationship defined between the two, see table structure below:

SELECT
VisitPk, (Primary Key)
ClientFk,
ClientSiteFk,
AssessorFk,
VisitStartDate,
VisitEndDate,
CONCAT(MONTHNAME(TargetDate), ' ', YEAR(TargetDate)) AS TargetMonth,
Duration,
VisitStatus,
TargetDate,
`Long`,
Lat,
VisitPlanRequired,
VisitPlanIssued,
VisitPlanStatus,
VisitPlanAttachment,
DATE_SUB(VisitStartDate, INTERVAL 1 Month) AS VisitPlanDue,
Overnight,
YEAR(TargetDate) AS `Year`
FROM visits
ORDER BY YEAR(TargetDate) DESC, TargetDate, VisitStartDate

and

SELECT
assessments.AssessmentPk, (Foreign Key)
assessments.VisitFk,
assessments.`Scope`,
FROM assessments

What I need is to put a field into the Assessment table so that it looks up all of the scopes from the visit table and then groups them. E.G is visit pk 1 has two entries 3 child records, 1, 2 and 3, id like to use a concat/implode function to show it as 1,2,3 in the parent table.

I could do this with PHP and physically store the data in the table, but I would prefer to not go down this route.

I have tried having a 2nd select statement in the first select statement to run a subquery but doesnt even come close.

I have also tried using this exmaple found on: Can I concatenate multiple MySQL rows into one field?, e.g.:

SELECT person_id, GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies GROUP BY person_id

Ultimately, what I need is for someone to tell me how i can place a 2nd select statement using the above group_contact example but by looking up the child table where assessmentfk=assessmentpk.

So something like:

SELECT
VisitPk, (Primary Key)
ClientFk,
ClientSiteFk,
**CONCAT(SELECT `scope` from assessments LEFT OUTER JOIN assessments ON visits.VisitPk = assessments.VisitFk) as `Scope`**
AssessorFk,
VisitStartDate,
VisitEndDate,
CONCAT(MONTHNAME(TargetDate), ' ', YEAR(TargetDate)) AS TargetMonth,
Duration,
VisitStatus,
TargetDate,
`Long`,
Lat,
VisitPlanRequired,
VisitPlanIssued,
VisitPlanStatus,
VisitPlanAttachment,
DATE_SUB(VisitStartDate, INTERVAL 1 Month) AS VisitPlanDue,
Overnight,
YEAR(TargetDate) AS `Year`
FROM visits
ORDER BY YEAR(TargetDate) DESC, TargetDate, VisitStartDate

I hope that this makes sense and that someone can help, this is far beyond anything I have done in the past so apologies in advance.

I have also tried (SELECT GROUP_CONCAT(`scope` SEPARATOR ', ') from assessments LEFT OUTER JOIN assessments ON visits.VisitPk = assessments.VisitFk GROUP BY `scope`) as `Scope`, — jaykay79, Jan 11 '17 at 18:56
and group_concat(`Scope`) as Standards from assessments where visits.VisitPk = assessments.VisitFk, — jaykay79, Jan 11 '17 at 19:10

score 0 · Accepted Answer · answered Jan 11 '17 at 20:13

try inner join of two tables and group by

      SELECT 
            VisitPk, ClientFk, ClientSiteFk,AssessorFk,VisitStartDate, VisitEndDate,
            GROUP_CONCAT(a.scope, ',') as scopes
            CONCAT(MONTHNAME(TargetDate), ' ', YEAR(TargetDate)) AS TargetMonth,
            Duration,VisitStatus,TargetDate,`Long`,Lat,VisitPlanRequired,
            VisitPlanIssued,VisitPlanStatus,VisitPlanAttachment,
            DATE_SUB(VisitStartDate, INTERVAL 1 Month) AS VisitPlanDue,
            Overnight,YEAR(TargetDate) AS `Year`
     FROM visits v
     INNER JOIN assessments a
     ON v.VisitPk = a. VisitFk
     GROUP BY v.VisitPk
     ORDER BY YEAR(TargetDate) DESC, TargetDate, VisitStartDate

select subquery for child table

1 Answers1