X-Received: by 10.236.28.230 with SMTP id g66mr12154257yha.2.1423416947724; Sun, 08 Feb 2015 09:35:47 -0800 (PST) X-Received: by 10.50.118.42 with SMTP id kj10mr170758igb.9.1423416947614; Sun, 08 Feb 2015 09:35:47 -0800 (PST) Path: csiph.com!usenet.pasdenom.info!usenet.blueworldhosting.com!feeder01.blueworldhosting.com!peer03.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!j7no393070qaq.0!news-out.google.com!qk8ni25600igc.0!nntp.google.com!hl2no12052065igb.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.ms-sqlserver Date: Sun, 8 Feb 2015 09:35:47 -0800 (PST) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=107.207.76.36; posting-account=eTE9_AoAAAD1dS9O9Ccywd_vfKFzS40A NNTP-Posting-Host: 107.207.76.36 References: User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <1f4f8d67-a6f4-4996-8a7e-aa78431a671d@googlegroups.com> Subject: Re: How to I select to show only those that have more than one Horse From: --CELKO-- Injection-Date: Sun, 08 Feb 2015 17:35:47 +0000 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Received-Bytes: 3110 X-Received-Body-CRC: 813361886 Xref: csiph.com comp.databases.ms-sqlserver:1885 >> One Rider can own many Horses but one Horse can only be owned by one Rid= er. 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. Fol= low industry and ANSI/ISO standards in your data. You should follow ISO-111= 79 rules for naming data elements. You should follow ISO-8601 rules for dis= playing 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 nee= d to read and download the PDF for:=20 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 element= s do not change their names from table to table.=20 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 h= ave to assume that horses also have a registration number, like we do in th= e 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 t= han 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.=20