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