Combining results from two tables

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

SQL Server 2000 Forums

SQL Server Development (2000)

Combining results from two tables

Author

Topic

Faiyth
Starting Member

19 Posts

Posted - 2005-08-29 : 12:50:03

have two tables and I want to return data from both. Currently my select statement is returning just 1 child record for each parent record and I want to return all child records that match the parent record.

Here's a sample of my tables/data/etc.

t1
------------
speciesid | species
1 | Mammals
2 | Rodents
3 | Reptiles

t2
---------
animalid | animal
3 | Skink
3 | Iguana
3 | Rattlesnake
2 | Meerkat
1 | Hippo
1 | Elk

What I want to do is pull up a list of all the species and under each list all the animals currently listed under that species.

So the result I want should look like:
Mammals (Hippo, Elk)
Reptiles (Skink, Iguana, Rattlesnake)
Rodents (Meerkat)

so currently I have:
SELECT A.animalid, S.speciesid, A.animal, S.species from t2 as A, t1 as S where S.speciesid=A.animalid order by species

this works great, it's just that it only returns one animal instead of all of the animals. Any help would be appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-29 : 13:28:37

Your query works fine for me. Is this for MS SQL Server?

Here's the code that I used:



CREATE TABLE t1 (speciesid int not null, species varchar(20) not null)
CREATE TABLE t2 (animalid int not null, animal varchar(20) not null)

INSERT INTO t1 VALUES(1, 'Mammals')
INSERT INTO t1 VALUES(2, 'Rodents')
INSERT INTO t1 VALUES(3, 'Reptiles')

INSERT INTO t2 VALUES(3, 'Skink')
INSERT INTO t2 VALUES(3, 'Iguana')
INSERT INTO t2 VALUES(3, 'Rattlesnake')
INSERT INTO t2 VALUES(2, 'Meerkat')
INSERT INTO t2 VALUES(1, 'Hippo')
INSERT INTO t2 VALUES(1, 'Elk')

SELECT a.animalid, s.speciesid, a.animal, s.species
FROM t2 a
INNER JOIN t1 s
ON s.speciesid = a.animalid 
ORDER By s.species

SELECT A.animalid, S.speciesid, A.animal, S.species from t2 as A, t1 as S where S.speciesid=A.animalid order by species

DROP TABLE t1, t2

The first SELECT query is the preferred way to join tables. The second is your query. Both return the same result set.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources