Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

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

Show all headers | View raw

>> One Rider can own many Horses but one Horse can only be owned by one Rider.
This query list all the relation between a Rider and a Horse. <,

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. And you need to read and download the PDF for: 
https://www.simple-talk.com/books/sql-books/119-sql-code-smells/

You also missed basic concepts. Tables are sets, so their names are plural. Do you really have only one rider and one horses, as you said? There is no generic "name" in RDBMS; it has to be the name of something in particular. Read ISO-11179 rules and learn what an Attribute Property is. Data elements do not change their names from table to table. 

CREATE TABLE Riders
(social_nbr CHAR(15) NOT NULL PRIMARY KEY,
 rider_name VARCHAR(35) NOT NULL);

CREATE TABLE Horses
(horse_nbr CHAR(15) NOT NULL PRIMARY KEY,
 horse_name VARCHAR(35) NOT NULL);

These are entities; ridership is a relationship and needs it own table. I have to assume that horses also have a registration number, like we do in the US. It is on an RFID tag embedded in the animal.

CREATE TABLE Ridership
(social_nbr CHAR(15) NOT NULL
  REFERENCES Ridership (social_nbr)
  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. 

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next 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