Need to show only when I have more then 2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need to show only when I have more then 2

Author

Topic

tojo
Starting Member

5 Posts

Posted - 2015-02-08 : 05:44:03

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

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-08 : 07:11:36

Maybe this:

select r.Name
      ,h.Name as Horsename
  from Rider as r
       inner join Horse as h
               on h.RiderID=r.SocSecNr
       inner join (select RiderID
                         ,count(*) as horses
                     from Horse
                    group by RiderID
                  ) as c
               on c.RiderID=r.SocSecNr
 where c.horses=2
 order by r.Name

tojo
Starting Member

5 Posts

Posted - 2015-02-08 : 07:34:44

This works but I want to show all that have more then one horse not
just exact two horses.

//Tony

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-08 : 07:38:49

change the where statement to
where c.horses>1

tojo
Starting Member

5 Posts

Posted - 2015-02-08 : 09:33:02

This was a nice solution.
I have not used join so much so I don't really understand the solution fully.
I just wonder is it possible to solve the problem without using join.

if not can you exaplain how the solution works in a simple way so I can understand it.

//Tony

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-08 : 10:21:21

Actually you yourself are performing a join in your original query.
Referring more than one table in the from section, will cause a join. Your join is a cross join, except your where section makes it an inner join. Now that we know it's a inner join, we might as well write it. This way we know exactly what you intended when you wrote the query.

Joins, "group by" and aggregations are very important, so I'd suggest you read up on these topics.

To answer your first question - No, it is not possible to write the query without join (at least the database engine performs the join, giving the fact that you're referring more than one table).

To explain the query, let us first have a look at it, where I have colourized portions:

select r.Name
      ,h.Name as Horsename
  from Rider as r
       inner join Horse as h
               on h.RiderID=r.SocSecNr
       inner join (select RiderID
                         ,count(*) as horses
                     from Horse
                    group by RiderID
                  ) as c
               on c.RiderID=r.SocSecNr
 where c.horses=2
 order by r.Name

Red portion: a subselect, which will give you a count (aggregation function) of horses per RiderID. Try running this portion only, to see the result.
Orange portion: aliases so you can refer to a field, table or subselect with another name. This is handy if you use the same table more than once in you query.
Green portion: referencing the aliases

Now, you can think of the subselect (red portion) as a table of its own containing RiderID and horses (this containt the number of horses a given rider has).

Hope this makes a little more sence now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources