lundi 29 décembre 2014

How to use SQLite group_concat(X, Y) to create a single column from a JOIN?

Forgive me if I'm not phrasing this question correctly. Here's what I'm trying to do:


I have two tables joined on a string column containing a GUID. Table 1 contains everything concerning my data object, while Table 2 contains a string column that I need to link to the record from Table 1. For every record in Table 1, there are 1..n records in Table 2. My current SQLite statement using an INNER JOIN is returning n records for each record in Table 1, and this doesn't work for my needs. I want to get back just 1 record for each record in Table 1.


However, the trick is that I still want to see all of those string values from Table 2, with the strings concatenated as "Value 1; Value 2; Value 3; etc".


I've tried using the group_concat(X, Y) function as follows:



SELECT o.ObservationGuid, o.ObservationID, o.ObservationParentTypeGuid,
o.ObservationTypeGuid, o.Name, o.ObservationParentName, o.GrowthStageGuid,
o.MorphologyTypeGuid, o.LifespanGuid, o.OrganismTypeGuid, o.ScientificName,
o.AgXID, o.AgGatewayID, o.Genus, o.Family, o.TaxonomicalOrder, o.Class,
o.Division, o.Kingdom, o.FavorableConditions, o.DescriptionSymptoms,
group_concat(oa.Name, '; ')
FROM Observation o
INNER JOIN ObservationAlias oa ON oa.ObservationGuid = o.ObservationGuid
WHERE ObservationTypeGuid = '{E3149781-2423-4F15-8599-CB320E871284}'
GROUP BY o.ObservationGuid COLLATE NOCASE


But this results in no records returned at all. Next, I tried removing the GROUP BY clause:



SELECT o.ObservationGuid, o.ObservationID, o.ObservationParentTypeGuid,
o.ObservationTypeGuid, o.Name, o.ObservationParentName, o.GrowthStageGuid,
o.MorphologyTypeGuid, o.LifespanGuid, o.OrganismTypeGuid, o.ScientificName,
o.AgXID, o.AgGatewayID, o.Genus, o.Family, o.TaxonomicalOrder, o.Class,
o.Division, o.Kingdom, o.FavorableConditions, o.DescriptionSymptoms,
group_concat(oa.Name, '; ')
FROM Observation o
INNER JOIN ObservationAlias oa ON oa.ObservationGuid = o.ObservationGuid
WHERE ObservationTypeGuid = '{E3149781-2423-4F15-8599-CB320E871284}'
COLLATE NOCASE


This returns 1 record, consisting of the first matching record from Table 1 plus the oa.Name field containing the string value of EVERY record in Table 2 in the format "Value 1; Value 2; Value 3; etc"


Any help will be greatly appreciated!


Aucun commentaire:

Enregistrer un commentaire