Results 1 to 5 of 5

Thread: Returning Multiple rows from 2 tables

  1. #1
    Join Date
    Dec 2002
    Posts
    3

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Your query is correct to return all the animals.

  3. #3
    Join Date
    Aug 2005
    Posts
    5

    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.

  4. #4
    Join Date
    Aug 2005
    Posts
    1

    Post 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

  5. #5
    Join Date
    Aug 2005
    Posts
    6
    Quote 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
  •