Re: SQL selecting in two related tables?

[oliver1804]

> -----Ursprüngliche Nachricht-----
> Von: "Jean-Denis Muys" <jdmuys@...>
> Gesendet: 06.11.09 16:06:22
> An: General Discussion of SQLite Database <sqlite-users@...>
> Betreff: Re: [sqlite] SQL selecting in two related tables?

>
> On 11/6/09 15:52 , "cmartin@..." <cmartin@...> 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;
> >
> >
>
> Amazing! I guess it'll take me a long time to understand how that one works.
> At this point, I am puzzled.
>
> In any case, I notice it doesn't *quite* do what I want. My fault: I didn't
> mention what should happen if there is no subscription for a magazine. In
> that case, I'd like it to be present with Issues shown as NULL. Your
> solution simply omits any magazine without a subscription.
>
> Is there a way to improve it so it includes them?
>

simply replace inner with left






>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@...
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


______________________________________________________
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://movieflat.web.de

_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users