Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail From: "Tony Johansson" Newsgroups: comp.databases.ms-sqlserver Subject: Re: How to I select to show only those that have more than one Horse Date: Sun, 8 Feb 2015 12:40:40 +0100 Organization: A noiseless patient Spider Lines: 42 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; format=flowed; charset="Windows-1252"; reply-type=original Content-Transfer-Encoding: 8bit Injection-Date: Sun, 8 Feb 2015 11:40:25 +0000 (UTC) Injection-Info: mx02.eternal-september.org; posting-host="8fdc2b5af81c338deb7045fc6c39b0e2"; logging-data="11632"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+QwWR60YKW8gKEdXVb8cOI" X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18463 In-Reply-To: X-Newsreader: Microsoft Windows Mail 6.0.6002.18197 Cancel-Lock: sha1:aHx/yByRAuSFqiPOi7mkvrdxkAo= X-Priority: 3 X-MSMail-Priority: Normal Xref: csiph.com comp.databases.ms-sqlserver:1882 How can I do this in MySQL or using a view. I want a more standard solution that works in more db then just SQL Server. //Tony "Erland Sommarskog" skrev i meddelandet news:XnsA43B778BA7558Yazorman@127.0.0.1... > Tony Johansson (johansson.andersson@telia.com) writes: >> 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. >> >> select Rider.Namn, Horse.Namn as Horsename >> from Rider, Horse >> where Horse.RiderID= Rider.SocNr >> order by Rider.Namn >> >> Name Horsename >> Adeline Falk Vilja F >> Anna Westman Solidago >> Anna Westman early morning grey >> Corola Häggqvist Com-on Landlyst > > Corola? Are you trying to avoid trade-mark infringement? :-) > > For instance: > > ; WITH CTE AS ( > SELECT R.Name, H.Namn AS Horsname, > COUNT(*) OVER(PARTITION BY R.SocNr) AS cnt > FROM Rider R > JOIN Horse H ON H.RiderID = R.SocNr > ) > SELECT Name, Horsename > FROM CTE > WHERE cnt >= 2 > > > -- > Erland Sommarskog, Stockholm, esquel@sommarskog.se