-
Returning Multiple rows from 2 tables
I 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.
-
Your query is correct to return all the animals.
-
Simpler Way By Arrypotter
Let me give you a better and a easier way,
Its lengthy but safe....
<<<<<
step 1
Just take table t2 and insert into a temp table #t2
>>>>>
drop table #t2
/* if #t2 exists */
create table #t2
(
animalid int,
animal varchar (20)
species varchar (20)
)
<<<<<<<
step2...
now insert into #t2
>>>>>>
insert into #t2
(
animalid ,
animal
)
select animalid ,animal from t2
<<<<<
step3...
now update species column
>>>>>
update #t2
set species = a.species
from #t2 b, t1 a
where a.speciesid = b.animalid
tahtz it ,
arvind
I 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.
-
PK-FK Rel.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Animal]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Animal]
GO
CREATE TABLE [dbo].[Animal] (
[AnimalID] [int] IDENTITY (1, 1) NOT NULL ,
[SpeciesID] [int] NULL ,
[Animal] [char] (10) COLLATE SQL_Latin1_General_CP437_BIN NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Animal] ADD
CONSTRAINT [PK_Animal] PRIMARY KEY CLUSTERED
(
[AnimalID]
) ON [PRIMARY]
GO
---------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Species]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Species]
GO
CREATE TABLE [dbo].[Species] (
[SpeciesID] [int] IDENTITY (1, 1) NOT NULL ,
[Species] [varchar] (50) COLLATE SQL_Latin1_General_CP437_BIN NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Species] WITH NOCHECK ADD
CONSTRAINT [PK_Species] PRIMARY KEY CLUSTERED
(
[SpeciesID]
) ON [PRIMARY]
GO
---------------------------------------------------------
INSERT INTO Species (Species) VALUES('Mammals')
INSERT INTO Species (Species) VALUES('Rodents')
INSERT INTO Species (Species) VALUES('Reptiles')
INSERT INTO Animal (SpeciesID, Animal) VALUES(3,'Skunk')
INSERT INTO Animal (SpeciesID, Animal) VALUES(3,'Iguana')
INSERT INTO Animal (SpeciesID, Animal) VALUES(3,'Rattle')
INSERT INTO Animal (SpeciesID, Animal) VALUES(2,'Meerket')
INSERT INTO Animal (SpeciesID, Animal) VALUES(2,'Hippo')
INSERT INTO Animal (SpeciesID, Animal) VALUES(1,'Elk')
---------------------------------------------------------
--Maybe this is how it should be done. Better PK-FK relationships set
SELECT
S.Species,
A.Animal
FROM
Species S INNER JOIN Animal A ON
S.SpeciesID = A.SpeciesID
ORDER BY
S.Species
-
Originally Posted by faiyth
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
If you replace the A.animalid with S.speciesid it should return all records...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|