CODEWITHSUNDEEP
sqlsqlite
GreenTriangle
GreenTriangle

Reputation: 2460

How do I subquery SQLite inside a GROUP_CONCAT query?

Apologies for the sloppy title, I can't quite think of a cleaner way to word it.

I have searched this issue and found people talking about it, but the examples are all with simple queries that aim to retrieve singular values, or beyond my grasp. I would be really appreciative if someone could tip me off as to how this can be done neatly.

Here's the SQLFiddle I made with my problem boiled down to remove all unnecessary elements. The table arrangement is

Books

Alternate_Titles

Tags

Books_Tags

Authors

Books_Authors

The query I'm trying to write would yield results like

Title, Author, Tags, Alternate_Titles
"Star Wars","George Lucas","sci-fi,70s,adventure","Star Wars: Episode IV, A New Hope"

The catch is that alternate_title.title values can contain commas. GROUP_CONCAT can't specify a different separator string while also having DISTINCT. So I either get a value I can't split apart in the client application, or a value containing many duplicates (since a book could have 5 alternate titles).

The solution is to write a SELECT subquery, but I can't for the life of me figure out how to write one for this situation, where I'm already joining multiple tables... The examples I find, I can't apply to my own example.

What should I do?

Upvotes: 0

Views: 314

Answers (2)

forpas
forpas

Reputation: 164069

Instead of joining all tables, and then aggregating, you could pre-aggregate in Alternate_Titles and in the joined Books_Tags and Tags and finally join the results, so there are no duplicates and you can use the separator argument inside GROUP_CONCAT():

SELECT b.Title, a.Name, titles, labels
FROM Books b
JOIN Books_Authors ba ON b.ID = ba.Books_ID
JOIN Authors a ON ba.Authors_ID = a.ID
JOIN (
  SELECT Books_ID, GROUP_CONCAT(Title, '^SEP^') titles
  FROM Alternate_Titles
  GROUP BY Books_ID
) at ON b.ID = at.Books_ID
JOIN (
  SELECT bt.Books_ID, GROUP_CONCAT(t.Label, '^SEP^') labels
  FROM Books_Tags bt JOIN Tags t 
  ON bt.Tags_ID = t.ID
  GROUP BY bt.Books_ID  
) t ON b.ID = t.Books_ID

See the demo (fixed (3, 'Romance') instead of (2, 'Romance') in Tags and (2, 3) instead of the 2nd (2, 2) in Books_Tags).

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use correlated subqueries:

SELECT b.Title,
       (SELECT GROUP_CONCAT(a.Name)
        FROM Books_Authors ba JOIN
             Authors a
             ON ba.Authors_ID = a.ID
        WHERE b.ID = ba.Books_ID
       ) as authors,
       (SELECT GROUP_CONCAT(alt.Title, '^SEP^') -- Should be DISTINCT
        FROM ALternate_Titles alt
        WHERE b.ID = alt.Books_ID
       ) as alternate_titles,
       (SELECT GROUP_CONCAT( t.Label)
        FROM Books_Tags bt JOIN
             Tags t
             ON bt.Tags_ID = t.ID
        WHERE b.ID = bt.Books_ID
       ) as tags
FROM Books b ;

Here is a SQL Fiddle.

Upvotes: 0

Related Questions