SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQLite group_concat question...

    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%?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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 --
    Code:
    FROM pictures p
    LEFT JOIN tags t ON p.id = t.fkPicture
    which is your ordinary left outer join

    however, you then add this --
    Code:
    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...
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by creole View Post
    I also tried removing the WHERE clause from the embedded join and that didn't change anything.
    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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hrm. Don't think I tried added a group by. I'll test that out tonight when I get home.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  6. #6
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    336
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    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:
    HTML Code:
    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:
    Code:
    WHERE tags != ''

  7. #7
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    336
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by creole View Post
    Hrm. Don't think I tried added a group by. I'll test that out tonight when I get home.

    Code:
    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

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    nice try, centered_effect, but your last query has the same problem as i mentioned

    this is better --
    Code:
    SELECT p.id
         , p.filename
         , p.author
         , GROUP_CONCAT(t.tag) as tags
      FROM pictures p
    LEFT OUTER
      JOIN tags t
        ON t.fkPicture = p.id
    GROUP 
        BY p.id
         , p.filename
         , p.author
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That seems to have done it Rudy. Could you help me understand why that works?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Both actually, but what's the difference between a left outer, and a left?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    the OUTER keyword is optional, that's all

    i always write it anyway, to remind me that it's an outer join
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •