1

I have a database of materials that is split into two tables, A and B, each of which contains the name of the material, a property and a reference for the property. Each material can have multiple properties and thus multiple references. I then join the two tables on the name of the material, resulting in a table with on row per material (and a column for each property).

The problem is that I would like to merge all references for a given material into a single, non-repetitive list. Right now I have a column with a group_concat(distinct) of all references for a material from table A and one column with all references for a material from table B. I can naturally manually concat them references_A || ", " || references_B but that does not get rid of duplicates.

Below is a minimal example, creating the first table:

CREATE TABLE "attributes" (
"name",
"attribute",
"value",
"reference"
);
INSERT INTO "attributes" VALUES ('DAY', 'Si/Al ratio', '∞', 'XYZ');
INSERT INTO "attributes" VALUES ('NaY', 'Si/Al ratio', '2.4', 'XYZ');

Creating the second table:

CREATE TABLE "properties" (
"name",
"Tmp",
"property",
"value",
"reference"
);
INSERT INTO "properties" VALUES ('DAY', 300, 'capacity', 5.36, 'XYZ');
INSERT INTO "properties" VALUES ('DAY', 320, 'capacity', 7.44, 'XYZ');
INSERT INTO "properties" VALUES ('NaY', 300, 'capacity', 6.8, 'ABC');
INSERT INTO "properties" VALUES ('NaY', 300, 'capacity', 9.4, 'ABC');

And creating the combined view:

CREATE VIEW base AS SELECT
    at.name,
    pr.Tmp,
    max(case when at.attribute = 'Si/Al ratio' then at.value end) `SiAl`,
    avg(case when pr.property = 'capacity' then pr.value end) `capacity`,
    group_concat(distinct pr.reference) `reference_pr`,
    group_concat(distinct at.reference) `reference_at`
FROM attributes at
LEFT JOIN properties pr ON
     at.name = pr.name
GROUP BY at.name, pr.Tmp

As to why I start with two tables see my previous question: https://stackoverflow.com/questions/8835443/sqlite-pivot-via-case-how-to-fill-down-a-column

Community
  • 1
  • 1
sseelenluft
  • 79
  • 1
  • 9
  • Could you use a subquery to do a Distinct or something ? – FMaz008 Feb 10 '12 at 14:11
  • As far as I understand `distinct` in SQLite, it only works on rows, ie, when condensing multiple rows into one, it ensures that there are no duplicate rows left, it does not work when combining columns (where only a manual concatenation works). If something like `SELECT distinct (column_A, columnB) 'newname'` worked, it would be the solution for me. – sseelenluft Feb 10 '12 at 14:25
  • Yeah, but using a subquery, in the deepest query, can you `concat` 2 columns as one.. then do a `distinct` on the higher query.. ? – FMaz008 Feb 10 '12 at 14:26
  • Sorry, edited my comment. What works naturally is `column_A || column_B`, but that is not distinct. – sseelenluft Feb 10 '12 at 14:28
  • I don't have sqlite environnement to test, but my idea is something like: `SELECT DISTINCT(sub.groupCol) FROM (SELECT a || b as groupCol) as sub` – FMaz008 Feb 10 '12 at 14:33
  • The problem is that due to my two-table starting point, I already start with two columns: at.reference and pr.reference. I can concat them manually, but distinct won't be able to merge then if one contains **XYZ, ABC** and the second **ABC**, the result at best will be **XYZ, ABC, ABC**. – sseelenluft Feb 10 '12 at 14:35
  • too bad you don't have enough points to chat. In my query, the distinct is only made on 1 field. The key is to manage the merge yourself using a subquery. – FMaz008 Feb 10 '12 at 14:38
  • I don't think that works in SQLite, at least the line `(SELECT a || b as groupCol) as sub` throws up on error. – sseelenluft Feb 10 '12 at 14:42
  • What's the error ? And remember I did pseudo code, you'll have to adapt it. But as far as I know, the concat syntax with an alias works: http://stackoverflow.com/questions/5932147/how-to-concat-columns-in-android-sqlite , as well as my subquery with also an alias: http://stackoverflow.com/questions/843604/subquery-in-sqlite-query-not-working – FMaz008 Feb 10 '12 at 14:47
  • Running `SELECT reference_at || reference_pr AS groupCol FROM base` works. Running `SELECT (reference_at || reference_pr AS groupCol) as sub FROM base` gives a syntax error near __AS__. – sseelenluft Feb 10 '12 at 15:06

0 Answers0