SQLite group_concat question

Databases
1

I’m building an AIR application and I’ve got a section which could benefit from the group_concat function.

I have a database with two tables:

CREATE TABLE pictures (
id INTEGER PRIMARY KEY AUTOINCREMENT,
author TEXT,
filename TEXT
)

and

CREATE TABLE tags (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tag TEXT, fkPIcture INTEGER
)

Which have the following data:

INSERT INTO pictures (id,author,filename) VALUES (2,‘calvinshe’,‘5015892459_0a6baa53d4_m.jpg’);
INSERT INTO pictures (id,author,filename) VALUES (3,‘rfhsphoto’,‘5019232052_f6d9160fd2_m.jpg’);
INSERT INTO pictures (id,author,filename) VALUES (4,‘Stefnisson’,‘5019232080_2004d50a17_m.jpg’);

and

INSERT INTO tags (id,tag,fkPIcture) VALUES (6,‘sunset’,4);
INSERT INTO tags (id,tag,fkPIcture) VALUES (7,‘birds’,4);
INSERT INTO tags (id,tag,fkPIcture) VALUES (8,‘clouds’,4);
INSERT INTO tags (id,tag,fkPIcture) VALUES (9,‘bride’,2);

You can see that photo 4 has 3 tags, while photo 2 has just one tag. What I’d like to end up with is a record for each photograph in the pictures table, and a commadelimited list of tags for each photo. Basically like this:

author filename tags
Stefnisson 5019232080_2004d50a17_m.jpg sunset, birds, clouds
calvinshe 5015892459_0a6baa53d4_m.jpg bride

Now here’s the query I’ve got right now. It almost works, but for some reason it’s grouping the tags into the first photo’s record.

SELECT p.id, p.filename, p.author, p.photoURL, COALESCE(t.tags, “”) AS tags
FROM pictures p
LEFT JOIN (
SELECT fkPicture, GROUP_CONCAT(t.tag) as tags
FROM pictures p
LEFT JOIN tags t ON p.id = t.fkPicture
WHERE t.fkPicture = p.id
) t ON t.fkPicture = p.id
ORDER BY p.id DESC

I’m sure it has to do with the LEFT JOIN in the subquery, but I can’t figure it out. Is there someone who could help me take this the last 5%?

2 

SELECT 
  	  p.id
	, p.filename
	, p.author
	, (SELECT
			GROUP_CONCAT(t.tag)
		FROM tags t
		WHERE t.fkPicture = p.id
	) AS tags
FROM pictures p
ORDER BY p.id DESC

Gives me the results:

id	filename				author		tags
4	5019232080_2004d50a17_m.jpg	Stefnisson	sunset,birds,clouds
3	5019232052_f6d9160fd2_m.jpg	rfhsphoto	
2	5015892459_0a6baa53d4_m.jpg	calvinshe	bride

Without the picture that doesn’t have any tags:

WHERE tags != ''
3

the OUTER keyword is optional, that’s all

i always write it anyway, to remind me that it’s an outer join :slight_smile:

4

Both actually, but what’s the difference between a left outer, and a left?

5

nice try, centered_effect, but your last query has the same problem as i mentioned

this is better –

SELECT p.id
     , p.filename
     , p.author
     , GROUP_CONCAT(t.tag) as tags
  FROM pictures p
[B][COLOR="Blue"]LEFT OUTER[/COLOR][/B]
  JOIN tags t
    ON t.fkPicture = p.id
GROUP 
    BY p.id
     , p.filename
     , p.author
6

question: do you know for sure that SQLite supports GROUP_CONCAT?

comment: your embedded LEFT OUTER JOIN will never return pictures that have no tags

this part is okay –

FROM pictures p
LEFT JOIN tags t ON p.id = t.fkPicture

which is your ordinary left outer join

however, you then add this –

WHERE t.fkPicture = p.id

which you will admit is never going to be true when p.id has no match, hence it’s an inner join

but why the outer query outer join from pictures to a nested subquery containing pictures?

something doesn’t look right

and wouldn’t you need a GROUP BY clause if you’re using GROUP_CONCAT? assuming, of course, that SQLite supports it…

7

Hrm. Don’t think I tried added a group by. I’ll test that out tonight when I get home.

8

That seems to have done it Rudy. Could you help me understand why that works?

9

it works because it’s a simple application of basic sql principles – whenever there are any non-aggregate expressions in the SELECT clause along with an aggregate, the non-aggregates need to be in the GROUP BY clause as well

or were you referring to the LEFT OUTER JOIN part of the solution?

10 


SELECT 
  	  p.id
	, p.filename
	, p.author
	, GROUP_CONCAT(t.tag) as tags
FROM pictures p
JOIN tags t
	ON t.fkPicture = p.id
GROUP BY  p.id
		, p.filename
		, p.author
ORDER BY  p.id DESC
11

only because you didn’t have comprehensive test data, because without the WHERE clause, you will also get pictures that have no tags at all

what happened when you added a GROUP BY clause?

12

Thanks for responding Rudy. Yes, it supports group_concat. In factm given the records listed above, the query I posted returns this:

author filename tags
Stefnisson 5019232080_2004d50a17_m.jpg bride, sunset, birds, clouds
calvinshe 5015892459_0a6baa53d4_m.jpg

Note that the tag for the second picture is included in the tag for the first picture.

I also tried removing the WHERE clause from the embedded join and that didn’t change anything.