Groups > comp.databases.ms-sqlserver > #1886

Re: How to I select to show only those that have more than one Horse

X-Received	by 10.66.136.205 with SMTP id qc13mr17666638pab.2.1423545696206; Mon, 09 Feb 2015 21:21:36 -0800 (PST)
X-Received	by 10.140.100.163 with SMTP id s32mr213721qge.41.1423545695950; Mon, 09 Feb 2015 21:21:35 -0800 (PST)
Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!usenet.blueworldhosting.com!feeder01.blueworldhosting.com!peer02.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!border1.nntp.dca1.giganews.com!nntp.giganews.com!hl2no9680673igb.0!news-out.google.com!c1ni0qar.1!nntp.google.com!v8no8020588qal.1!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.ms-sqlserver
Date	Mon, 9 Feb 2015 21:21:35 -0800 (PST)
In-Reply-To	<1f4f8d67-a6f4-4996-8a7e-aa78431a671d@googlegroups.com>
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=173.61.135.70; posting-account=SOVadwoAAAB3h7W1MLW9kMYtEc2JW2L8
NNTP-Posting-Host	173.61.135.70
References	<mb685k$o80$1@dont-email.me> <1f4f8d67-a6f4-4996-8a7e-aa78431a671d@googlegroups.com>
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<954a798f-edca-4b84-a9d9-bd4f5f196f09@googlegroups.com> (permalink)
Subject	Re: How to I select to show only those that have more than one Horse
From	Ross Presser <rpresser@gmail.com>
Injection-Date	Tue, 10 Feb 2015 05:21:35 +0000
Content-Type	text/plain; charset=ISO-8859-1
Lines	29
X-Received-Bytes	2146
X-Received-Body-CRC	2279761887
Xref	csiph.com comp.databases.ms-sqlserver:1886

Show key headers only | View raw

On Sunday, February 8, 2015 at 12:35:49 PM UTC-5, --CELKO-- wrote:
[snip]
> CREATE TABLE Ridership
> (social_nbr CHAR(15) NOT NULL
>   REFERENCES Ridership (social_nbr)
               ^^^^^^^^^
What?
You meant Riders, right?

Furthermore, he stated that each horse is only owned (or ridden)
by one Rider. You haven't captured that in your DDL.

>   ON UPDATE CASCADE
>   ON DELETE CASCADE,
>  horse_nbr CHAR(15) NOT NULL
>   REFERENCES Horse(horse_nbr)
>   ON UPDATE CASCADE
>   ON DELETE CASCADE,
> PRIMARY KEY (rider_nbr, horse_nbr));
> 
> >> How do I change this query if I only want to list those that have more than one horse. <<
> 
> SELECT social_nbr
>   FROM Ridership
>  GROUP BY social_nbr
> HAVING COUNT(horse_nbr) > 1;
> 
> 85-90% of the work in SQL is in the DDL, not the DML.

And the last 10% of helping someone is not messing up the answer.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

How to I select to show only those that have more than one Horse "Tony Johansson" <johansson.andersson@telia.com> - 2015-02-08 00:47 +0100
  Re: How to I select to show only those that have more than one Horse "Tony Johansson" <johansson.andersson@telia.com> - 2015-02-08 09:58 +0100
    Re: How to I select to show only those that have more than one Horse "Tony Johansson" <johansson.andersson@telia.com> - 2015-02-08 10:03 +0100
  Re: How to I select to show only those that have more than one Horse Erland Sommarskog <esquel@sommarskog.se> - 2015-02-08 11:45 +0100
    Re: How to I select to show only those that have more than one Horse "Tony Johansson" <johansson.andersson@telia.com> - 2015-02-08 12:40 +0100
      Re: How to I select to show only those that have more than one Horse Erland Sommarskog <esquel@sommarskog.se> - 2015-02-08 16:22 +0100
        Re: How to I select to show only those that have more than one Horse rja.carnegie@gmail.com - 2015-02-08 09:27 -0800
  Re: How to I select to show only those that have more than one Horse --CELKO-- <jcelko212@earthlink.net> - 2015-02-08 09:35 -0800
    Re: How to I select to show only those that have more than one Horse Ross Presser <rpresser@gmail.com> - 2015-02-09 21:21 -0800

csiph-web