sqlx structscan join query

I'm new to go and sqlx and I would want to know if its posible to strutscan a master -> detail join clause in one query. A patient has many episodes so I want to query all patients and embed an Episode array in each Patient struct

Something like this:

type Episode struct {
		EpisodeID int    `db:"episode_id"`
		PatientID int    `db:"patient_id"`
		Status    string `db:"status"`
	}

	type Patient struct {
		PatientID    int            `db:"patient_id"`
		FirstName    string         `db:"first_name"`
		MiddleName   sql.NullString `db:"middle_name"`
		LastName     string         `db:"last_name"`
		Episodes     []Episode
	}

	query := `SELECT 
	mr_patients.patient_id, 
	mr_patients.first_name, 
	mr_patients.middle_name, 
	mr_patients.last_name, 
	mr_episode_statuses.status, 
	mr_episodes.episode_id, 
	mr_episodes.created_at 
	FROM mr_patients 
	INNER JOIN mr_episodes ON mr_patients.patient_id = mr_episodes.patient_id 
	INNER JOIN mr_episode_statuses ON mr_episodes.status_id = mr_episode_statuses.status_id
	ORDER BY mr_patients.patient_id, mr_episodes.episode_id`

	rows, err := db.Queryx(query)

	if err != nil {
		log.Fatal(err)
	}

	var patients []Patient

	for rows.Next() {
		var p Patient
		err = rows.StructScan(&p)
		if err != nil {
			log.Fatalln(err)
		} else {
			patients = append(patients, p)
		}
	}

Thanks.

It's not going to work since database/sql or sqlx aren't object relational mappers and don't know how to "populate" your type []Episodes. sqlx can assign single struct fields within a parent struct with the "structName.fieldName" syntax but that's it. It can't take all your rows, know what is what and assign all Episodes to the correct Patient. You need an ORM for that.