This is not a question about C# but a simple question about SQL query

  • Thread starter Thread starter Tony Johansson
  • Start date Start date
T

Tony Johansson

This is about horse jumping.
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
Foreign key is RiderId

This query list all the relation between a Rider and a Horse.

select Rider.Name, Horse.Name as Horsename
from Rider, Horse
where Horse.RiderId= Rider.SocSecNr
order by Rider.Name

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.

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
 
This is about horse jumping.
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
Foreign key is RiderId

This query list all the relation between a Rider and a Horse.

select Rider.Name, Horse.Name as Horsename
from Rider, Horse
where Horse.RiderId= Rider.SocSecNr
order by Rider.Name


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.
Click to expand...

Not really sure how your database is defined, but you probably need to
use COUNT

SQL query for finding records where count > 1
http://stackoverflow.com/questions/7151401/sql-query-for-finding-records-where-count-1

// Anders
 
This is about horse jumping.
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
Foreign key is RiderId

This query list all the relation between a Rider and a Horse.

select Rider.Name, Horse.Name as Horsename
from Rider, Horse
where Horse.RiderId= Rider.SocSecNr
order by Rider.Name

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.

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
Click to expand...

I would be very tempted to do it in the application (C#). When you
retrieve rows then skip rows with single horse owners.

If you want to do it in SQL then try:

select r.Name, h1.Name
from Rider r, Horse h1
where h1.RiderId=r.SocSecNr
and (select count(*) from horse h2 where h2.RiderId=h1.RiderId) > 1
order by r.Name

Arne
 
I would be very tempted to do it in the application (C#). When you
retrieve rows then skip rows with single horse owners.

If you want to do it in SQL then try:

select r.Name, h1.Name
from Rider r, Horse h1
where h1.RiderId=r.SocSecNr
and (select count(*) from horse h2 where h2.RiderId=h1.RiderId) > 1
order by r.Name
Click to expand...

Something else.

If SocSecNr is what I think it is, then you will have a lot
of tough legal requirements to deal with. Maybe a generated id
is better.

Arne
 
It works fine Arne!
many thanks

//Tony


Arne Vajhøj said:
Something else.

If SocSecNr is what I think it is, then you will have a lot
of tough legal requirements to deal with. Maybe a generated id
is better.

Arne
Click to expand...
 
Arne Vajhoj to Tony Johansson:
This query list all the relation between a Rider
and a Horse.

select Rider.Name, Horse.Name as Horsename
from Rider, Horse
where Horse.RiderId= Rider.SocSecNr
order by Rider.Name

[...]
How do I change this query if I only want to
list those that have more then one horse.
Click to expand...

I would be very tempted to do it in the applica-
tion (C#). When you retrieve rows then skip rows
with single horse owners.
Click to expand...

That would be slower because the application would
have to make as many queries to the database as
there are riders, plus one. It may or may not be a
problem.
If you want to do it in SQL then try:

select r.Name, h1.Name
from Rider r, Horse h1
where h1.RiderId=r.SocSecNr
and (select count(*) from horse h2 where h2.RiderId=h1.RiderId) > 1
order by r.Name
Click to expand...

The correlated subquery will slow things down be-
cause it will be executed for every row of the main
query. I should like to propose another way to do
it:

SELECT Rider.Name, Horse.Name
FROM
( SELECT SocSecNr
FROM Rider
JOIN Horse ON Horse.RiderId = Rider.SocSecNr
GROUP BY SocSecNr
HAVING SUM(1) > 1
) RidersWithSeveralHorses
JOIN Rider ON Rider.SocSecNr = RidersWithSeveralHorses.SocSecNr
JOIN Horse ON RiderId = RidersWithSeveralHorses.SocSecNr
 
El lunes, 9 de febrero de 2015, 9:41:00 (UTC+1), Anton Shepelev escribió:
Arne Vajhoj to Tony Johansson:
This query list all the relation between a Rider
and a Horse.

select Rider.Name, Horse.Name as Horsename
from Rider, Horse
where Horse.RiderId= Rider.SocSecNr
order by Rider.Name

[...]
How do I change this query if I only want to
list those that have more then one horse.
Click to expand...

I would be very tempted to do it in the applica-
tion (C#). When you retrieve rows then skip rows
with single horse owners.
Click to expand...

That would be slower because the application would
have to make as many queries to the database as
there are riders, plus one. It may or may not be a
problem.
If you want to do it in SQL then try:

select r.Name, h1.Name
from Rider r, Horse h1
where h1.RiderId=r.SocSecNr
and (select count(*) from horse h2 where h2.RiderId=h1.RiderId) > 1
order by r.Name
Click to expand...

The correlated subquery will slow things down be-
cause it will be executed for every row of the main
query.
Click to expand...

It will slow it down because the 'h2.RiderId=h1.RiderId' clause.
I should like to propose another way to do
it:

SELECT Rider.Name, Horse.Name
FROM
( SELECT SocSecNr
FROM Rider
JOIN Horse ON Horse.RiderId = Rider.SocSecNr
GROUP BY SocSecNr
HAVING SUM(1) > 1
) RidersWithSeveralHorses
JOIN Rider ON Rider.SocSecNr = RidersWithSeveralHorses.SocSecNr
JOIN Horse ON RiderId = RidersWithSeveralHorses.SocSecNr
Click to expand...

Another fast option would be:

and RiderId in (select h2.RiderId from horse h2 group by h2.RiderId having count(h2.RiderId) > 1)

Old join vs subquery dilema. Hard to tell wich is best. Here is a nice postabout subquery performance cause it depends on amount of data, database indexes... Here is a good post about the subject http://stackoverflow.com/a/17490323
 
Back
Top