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

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

Newsgroups	comp.databases.ms-sqlserver
Date	2015-02-09 21:21 -0800
References	<mb685k$o80$1@dont-email.me> <1f4f8d67-a6f4-4996-8a7e-aa78431a671d@googlegroups.com>
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>

Show all headers | 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