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
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments