Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.databases.ms-sqlserver > #1882
| From | "Tony Johansson" <johansson.andersson@telia.com> |
|---|---|
| Newsgroups | comp.databases.ms-sqlserver |
| Subject | Re: How to I select to show only those that have more than one Horse |
| Date | 2015-02-08 12:40 +0100 |
| Organization | A noiseless patient Spider |
| Message-ID | <mb7hv9$bbg$1@dont-email.me> (permalink) |
| References | <mb685k$o80$1@dont-email.me> <XnsA43B778BA7558Yazorman@127.0.0.1> |
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" <esquel@sommarskog.se> 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
Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar
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