subquery - SQL Server Forums

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

subquery

Author

Topic

mary H
Starting Member

32 Posts

Posted - 2008-10-10 : 01:29:15

I have 2 tables, horse and entry.

Horse table looks like this:

Horse_id-Name-Sire
101-rotty-201
102-rose-201
201-stallion-401
301-flower-401
401-roxy-9999
501-bell-9999
502-sam-9999
9999-unknown-9999

Entry table loooks like this:

Event_id - Horse_id

101-101
101-102
101-201
101-301
102-201
103-201
201-101
301-301
401-102

I need to list the horse_id and name of those horses that were entered in the same event as their sire using a subquery.

Any clues or suggestions?

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-10 : 01:45:55

Select Horse_id,Name from Horse inner join Entry on Horse.Horse_id=Entry.Horse_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 01:56:27

[code]SELECT h.*
FROM Horse h
INNER JOIN Event e
ON e.Horse_id=h.Horse_id
INNER JOIN Event e1
ON e1.Event_id=e.Event_id
AND e1.Horse_id=h.Sire[/code]

mary H
Starting Member

32 Posts

Posted - 2008-10-10 : 04:43:21

GREAT IT WORKED PERFECTLY!!

THANKS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 04:51:06

quote:
Originally posted by mary H

GREAT IT WORKED PERFECTLY!!

THANKS

which suggestion? first or mine?

mary H
Starting Member

32 Posts

Posted - 2008-10-10 : 05:20:41

quote:
Originally posted by visakh16

quote:
Originally posted by mary H

GREAT IT WORKED PERFECTLY!!

THANKS

which suggestion? first or mine?

The first suggestion only returned the horse_id and name of every horse in the entry table. Thanks for ur suggestion wormz666

Your suggestion returned those horses that entered in the same event as their sire!

Thanks visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 05:21:36

Ok..Cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-10 : 06:21:44

In the future, PLEASE post proper sample data to make things easier for us to help you.
Post sample code like this.

DECLARE	@Horses TABLE
	(
		horseID INT,
		Name VARCHAR(10),
		sireID INT
	)

INSERT	@Horses
SELECT	101, 'Flash', 201 UNION ALL
SELECT	102, 'star', 201 UNION ALL
SELECT	201, 'boxer', 401 UNION ALL
SELECT	301, 'daisy', 401 UNION ALL
SELECT	401, 'snowyv', 9999 UNION ALL
SELECT	501, 'bluebell', 9999 UNION ALL
SELECT	502, 'sally', 9999 UNION ALL
SELECT	9999, 'unknown', 9999

DECLARE	@Entries TABLE
	(
		eventID INT,
		horseID INT
	)

INSERT	@Entries
SELECT	101, 101 UNION ALL
SELECT	101, 102 UNION ALL
SELECT	101, 201 UNION ALL
SELECT	101, 301 UNION ALL
SELECT	102, 201 UNION ALL
SELECT	103, 201 UNION ALL
SELECT	201, 101 UNION ALL
SELECT	301, 301 UNION ALL
SELECT	401, 102

SELECT		h.horseID,
		h.Name
FROM		@Horses AS h
INNER JOIN	@Entries AS e ON e.horseID IN (h.horseID, h.sireID)
GROUP BY	h.horseID,
		h.Name

E 12°55'05.63"
N 56°04'39.26"

mary H
Starting Member

32 Posts

Posted - 2008-10-10 : 06:30:52

quote:

Originally posted by Peso

In the future, PLEASE post proper sample data to make things easier for us to help you.
Post sample code like this.

DECLARE	@Horses TABLE
	(
		horseID INT,
		Name VARCHAR(10),
		sireID INT
	)

INSERT	@Horses
SELECT	101, 'Flash', 201 UNION ALL
SELECT	102, 'star', 201 UNION ALL
SELECT	201, 'boxer', 401 UNION ALL
SELECT	301, 'daisy', 401 UNION ALL
SELECT	401, 'snowyv', 9999 UNION ALL
SELECT	501, 'bluebell', 9999 UNION ALL
SELECT	502, 'sally', 9999 UNION ALL
SELECT	9999, 'unknown', 9999

DECLARE	@Entries TABLE
	(
		eventID INT,
		horseID INT
	)

INSERT	@Entries
SELECT	101, 101 UNION ALL
SELECT	101, 102 UNION ALL
SELECT	101, 201 UNION ALL
SELECT	101, 301 UNION ALL
SELECT	102, 201 UNION ALL
SELECT	103, 201 UNION ALL
SELECT	201, 101 UNION ALL
SELECT	301, 301 UNION ALL
SELECT	401, 102

SELECT		h.horseID,
		h.Name
FROM		@Horses AS h
INNER JOIN	@Entries AS e ON e.horseID IN (h.horseID, h.sireID)
GROUP BY	h.horseID,
		h.Name

E 12°55'05.63"
N 56°04'39.26"

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources