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

Groups > comp.databases.ms-sqlserver > #1878 > unrolled thread

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

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#1878 — How to I select to show only those that have more than one Horse

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
Emil Olsson             Thea
Emil Olsson             Innocentia
Fantomen                 Fighter
Natalie Adelborg     Femerick
Sanna Johansson     Karavella
Sara Ignberg          Trisdan
Stålmannan             Marshall
Stålmannan             Oliver
Sven Tumba             Rasmus
Sven Tumba             Lukan

How do I change this query if I only want to list those that have more then 
one horse.
So the result from the query should be this.

So the result would be
Anna Westman         Solidago
Anna Westman         early morning grey
Emil Olsson             Thea
Emil Olsson             Innocentia
Stålmannan             Marshall
Stålmannan             Oliver
Sven Tumba             Rasmus
Sven Tumba             Lukan

//Tony

[toc] | [next] | [standalone]

#1879

> One Rider can own many Horses but one Horse can only be owned by one 
> Rider.
The Rider Table have these columns Social security number, Name, Phone, 
E-mail, A path to an image, Description
The Horse have these columns Id, Name, A path to an image, Description,

> 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
> Emil Olsson             Thea
> Emil Olsson             Innocentia
> Fantomen                 Fighter
> Natalie Adelborg     Femerick
> Sanna Johansson     Karavella
> Sara Ignberg          Trisdan
> Stålmannan             Marshall
> Stålmannan             Oliver
> Sven Tumba             Rasmus
> Sven Tumba             Lukan
>
> How do I change this query if I only want to list those that have more 
> then one horse.
> So the result from the query should be this.
>
> So the result would be
> Anna Westman         Solidago
> Anna Westman         early morning grey
> Emil Olsson             Thea
> Emil Olsson             Innocentia
> Stålmannan             Marshall
> Stålmannan             Oliver
> Sven Tumba             Rasmus
> Sven Tumba             Lukan
>
> //Tony
> 

[toc] | [prev] | [next] | [standalone]

#1880

"Tony Johansson" <johansson.andersson@telia.com> skrev i meddelandet 
news:mb78g2$a0p$1@dont-email.me...
>
>
>> One Rider can own many Horses but one Horse can only be owned by one 
>> Rider.
> The Rider Table have these columns SocSecNr, Name, Phone, E-mail, A path 
> to an image, Description.
Primary key is SocSecNr

> The Horse have these columns Id, Name, A path to an image, Description
Primary key is Id

>> 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.SocSecNr
>> order by Rider.Namn
>>
>> Name                      Horsename
>> Adeline Falk           Vilja F
>> Anna Westman         Solidago
>> Anna Westman         early morning grey
>> Corola Häggqvist     Com-on Landlyst
>> Emil Olsson             Thea
>> Emil Olsson             Innocentia
>> Fantomen                 Fighter
>> Natalie Adelborg     Femerick
>> Sanna Johansson     Karavella
>> Sara Ignberg          Trisdan
>> Stålmannan             Marshall
>> Stålmannan             Oliver
>> Sven Tumba             Rasmus
>> Sven Tumba             Lukan
>>
>> How do I change this query if I only want to list those that have more 
>> then one horse.
>> So the result from the query should be this.
>>
>> So the result would be
>> Anna Westman         Solidago
>> Anna Westman         early morning grey
>> Emil Olsson             Thea
>> Emil Olsson             Innocentia
>> Stålmannan             Marshall
>> Stålmannan             Oliver
>> Sven Tumba             Rasmus
>> Sven Tumba             Lukan
>>
>> //Tony
>>
> 

[toc] | [prev] | [next] | [standalone]

#1881

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

[toc] | [prev] | [next] | [standalone]

#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" <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 

[toc] | [prev] | [next] | [standalone]

#1883

Tony Johansson (johansson.andersson@telia.com) writes:
> 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. 

Well, this is a forum for SQL Server, so here you will get answers for
SQL Server. If you want answers for MySQL, you need to try a MySQL forum.

However, I believe that the query I posted is compliant with the SQL
standard, and does not include any proprietary constructs. On the other
hand, just because a query is standards-compliant is no guarantee that
it will run on all engines.

-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#1884

On Sunday, 8 February 2015 15:22:23 UTC, Erland Sommarskog  wrote:
> Tony Johansson (johansson.andersson@telia.com) writes:
> > 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. 
> 
> Well, this is a forum for SQL Server, so here you will get answers for
> SQL Server. If you want answers for MySQL, you need to try a MySQL forum.
> 
> However, I believe that the query I posted is compliant with the SQL
> standard, and does not include any proprietary constructs. On the other
> hand, just because a query is standards-compliant is no guarantee that
> it will run on all engines.

I think that "WITH blah AS (yadda yadda yadda) SELECT FROM blah"
is the same as "SELECT FROM (yadda yadda yadda) blah", the point
being... well, various; having defined "blah" separaely,
you can use it more than once in the statement, you can 
vary its definition while keeping the later term unchanged,
or you can just regard it as "simplifying" the
statement as a whole, partitioning the logic, and 
making it more comprehensible overall, if perhaps 
not any more efficient in execution.

Having said that - and with continuing curiosity about 
whether we're doing Tony's college homework for him,
or alternatively when the sporting season starts and
he needs this stuff working - I think my effort 
would include in the first draft,

WHERE ( RiderID IN 
        (
        SELECT RiderID FROM Horse GROUP BY RiderID HAVING ( COUNT(*) > 1 )
        )
    )

This is another way of separating the problem of
"rider that is the rider of more than one horse" 
from the reporting.  But, then again, you can
"GROUP BY" lots of columns in a statement.
I worry that that's inefficient - but, on the
other other hand, my "RiderID IN (...)" clause
is something that a server might not handle well.

On the other other other hand, it may or may not
avoid behaviour that I think I've noticed, at least
up to SQL Server 2005, that writing 
"SELECT sq.* FROM ( SELECT <nested sub-query> ) sq"
tends to be equivalent to "SET QUERY OPTIMIZER OFF" - 
I mean that the server is likely to not do well 
at finding an efficient execution plan for the 
query as a whole, and, in particular, to make
proper use of indexes etc. on the inner statement's
objects in the outer statement.

So, asking the inner statement only to provide
a heap of RiderID may avoid that failure.

[toc] | [prev] | [next] | [standalone]

#1885

>> 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. 

[toc] | [prev] | [next] | [standalone]

#1886

On Sunday, February 8, 2015 at 12:35:49 PM UTC-5, --CELKO-- wrote:
[snip]
> CREATE TABLE Ridership
> (social_nbr CHAR(15) NOT NULL
>   REFERENCES Ridership (social_nbr)
               ^^^^^^^^^
What?
You meant Riders, right?

Furthermore, he stated that each horse is only owned (or ridden)
by one Rider. You haven't captured that in your DDL.

>   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.

And the last 10% of helping someone is not messing up the answer.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web