David542 David542 - 5 months ago 17
SQL Question

How to do a subquery in group_concat

I have the following data model:

`title`

- id
- name

`version`

- id
- name
- title_id

`version_price`

- id
- version_id
- store
- price


And here is an example of the data:

`title`

- id=1, name=titanic
- id=2, name=avatar

`version`

- id=1, name="titanic (dubbed in spanish)", title_id=1
- id=2, name="avatar directors cut", title_id=2
- id=3, name="avatar theatrical", title_id=2

`version_price`

- id=1, version_id=1, store=itunes, price=$4.99
- id=1, version_id=1, store=google, price=$4.99
- id=1, version_id=2, store=itunes, price=$5.99
- id=1, version_id=3, store=itunes, price=$5.99


I want to construct a query that will give me all titles that have a version_price on iTunes but not on Google. How would I do this? Here is what I have so far:

select
title.id, title.name, group_concat(distinct store order by store)
from
version inner join title on version.title_id=title.id inner join version_price on version_price.version_id=version.id
group by
title_id


This gives me a group_concat which shows me what I have:

id name group_concat(distinct store order by store)
1 Titanic Google,iTunes
2 Avatar iTunes


But how would I construct a query to include whether the item is on Google (using a case statement or whatever's needed)

id name group_concat(distinct store order by store) on_google
1 Titanic Google,iTunes true
2 Avatar iTunes false


It would basically be doing a
group_concat LIKE '%google%'
instead of a normal where clause.

Here's a link for a SQL fiddle of the current query I have: http://sqlfiddle.com/#!9/e52b53/1/0

vkp vkp
Answer

Use conditional aggregation to determine if the title is in a specified store.

select title.id, title.name, group_concat(distinct version_price.store order by store),
if(count(case when store = 'google' then 1 end) >= 1,'true','false') as on_google
from version 
inner join title on version.title_id=title.id 
inner join version_price on version_price.version_id=version.id
group by title.id, title.name

count(case when store = 'google' then 1 end) >= 1 counts all the rows for a given title after assigning 1 to the rows which have google in them. (Or else they would be assigned null and the count ignores nulls.) Thereafter, the if checks for the countand classifies a title if it has atleast one google store on it.