Your requirement is not quite clear to me: " I want the number of issues
in a subscrpition for each magazine". The magazine whose id is n086 has
two subscriptions in your example. Is it possible for a given magazine
to have subscriptions with a different number of issues? E.g. a one-year
monthly subscription and a two-year monthly subscription? Typically,
yes. So if you want to know all the possible subscription issue count
variations for each issn, you could do this:
select distinct issn, issues from subscription
Of course, you want to know the title of the magazine not just the issn,
so you could turn the first query above into an inline view and join it
to your magazine titles table:
select M.title
from Magazine as M
join
(
select distinct issn, issues from subscription
) as SubscriptionVariants
on SubscriptionVariants.issn = M.issn
order by M.title, SubscriptionVariants.issues
Regards
Tim Romano
cmar...@unc.edu wrote:
> On Fri, 6 Nov 2009, Jean-Denis Muys wrote:
>
>> I have two related tables, for example:
>>
>> Magazine
>> ===
>> ISSN Title
>> --
>> x123 Dr Dobb's
>> e753 Times
>> n086 National Geo
>>
>> Subscription
>> =
>> ISSN Issues StartDate EndDate
>> ---
>> x123 13 2/5/09 2/5/10
>> e753 52 Š ...
>> x123 13 Š ...
>> x123 13 Š ...
>> n086 12 Š ...
>> n086 12 Š ...
>>
>> (there is a one to many relation from Magazine to Subscription)
>>
>> And I want the number of issues in a subscrpition for each magazine:
>>
>> Issues per sub
>> =
>> Title Issues
>> --
>> Dr Dobb's 13
>> Times 52
>> National Geo 12
>>
>
> Assuming you have two tables as shown, with table names Magazines and
> Issues, and your column names are as shown, this should yield what you
> want:
>
> select Magazines.Title, sum(Issues) as Issues
> from Magazines inner join Subscription
> on Magazines.ISSN = Subscription.ISSN
> group by Magazines.Title;
>
>
> Chris
>
>
>>
>> I tried a number of variations of select, but I couldn't manage to
>> find the
>> answer... Yes, I know I need to read a few books. I am on my way.
>>
>>
>> Many thanks
>>
>> Jean-Denis
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.425 / Virus Database: 270.14.52/2484 - Release Date: 11/06/09
> 07:38:00
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
