|
SQL selecting in two related tables?
|
View:
New views
6 Messages
—
Rating Filter:
Alert me
|
 |
SQL selecting in two related tables?
by Jean-Denis Muys-2
::
Rate this Message:
Hi,
As an SQL rookie, I am struggling with the following, which I'm sure is very simple. 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 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@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: SQL selecting in two related tables?
by cmartin-2
::
Rate this Message:
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 > 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@... > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: SQL selecting in two related tables?
by Jean-Denis Muys-2
::
Rate this Message:
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? In any case, many many thanks. Jean-Denis _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: SQL selecting in two related tables?
by Swithun Crowe
::
Rate this Message:
Hello
JM > select Magazines.Title, sum(Issues) as Issues JM > from Magazines inner join Subscription JM > on Magazines.ISSN = Subscription.ISSN JM > group by Magazines.Title; JM In any case, I notice it doesn't *quite* do what I want. My fault: I JM didn't mention what should happen if there is no subscription for a JM magazine. In that case, I'd like it to be present with Issues shown as JM NULL. Your solution simply omits any magazine without a subscription. For that you want to change the 'inner join' to 'left join'. Swithun. _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: SQL selecting in two related tables?
by Tim Romano
::
Rate this Message:
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 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; > > > 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@... >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ------------------------------------------------------------------------ > > _______________________________________________ > sqlite-users mailing list > sqlite-users@... > 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@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: SQL selecting in two related tables?
by Tim Romano
::
Rate this Message:
I had responded to Jean-Denis Muys as follows:
> 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 > > But I neglected to add the issues column to the outer select; it should have read: select M.title, SubscriptionVariants.issues 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 _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
| Free embeddable forum powered by Nabble | Forum Help |
