Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.databases.ms-sqlserver > #1881
| From | Erland Sommarskog <esquel@sommarskog.se> |
|---|---|
| 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 11:45 +0100 |
| Organization | Erland Sommarskog |
| Message-ID | <XnsA43B778BA7558Yazorman@127.0.0.1> (permalink) |
| References | <mb685k$o80$1@dont-email.me> |
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