Help with one to many relationship

  • I'm trying to create a report in BIDS 2008 where the data has many one to many relationships and I'm wondering how to handle this in the report.

    Basically the database stores data from jobs that wipe hard drives, so each job can have one to N drives that have been wiped there also are seven custom fields that get populated per job. I wrote all the basic select statements out in SSMS to see if I got all the data I needed but now I'm having trouble getting it all together in the report.

    Here is all the basic statements that need to go into the Report

    Declare @p1 int

    Set @p1 = 1 --1 = first job that was run

    select * from Erasure

    where ID = @p1

    --one to many relationship

    select * From CustomerField

    where ErasureID = @p1

    --one to many relationship

    select

    'Disk:' + ' ' + convert(nvarchar(10), M.ID)

    ,M.Model

    ,M.Serial

    ,M.MediaSize / 1073741824 as Capacity

    ,M.[BlockSize]

    ,M.MediaType

    ,M.ErasureStatus

    ,M.SmartStatus

    ,M.UnlockStr

    ,M.LockingType

    from Media M

    INNER JOIN [Partition] P ON M.ComputerID = P.MediaID

    where m.ComputerID = @p1

    --one to many relationship

    select

    P.PartitionType

    ,P.PartitionType

    from [Partition] P

    INNER JOIN Media M ON P.MediaID = M.ID

    where m.ComputerID = @p1

    --one to many relationship

    select * from Computer

    where ID = @p1

    --one to many relationship

    select * from Ram

    where ComputerID = @p1

    --one to many relationship

    select * from cpu

    where ComputerID = @p1

    --one to many relationship

    select * from Device

    where ComputerID = @p1

  • The first thing I would probably do is to write all of this into one statement joining all your tables (Erasure to CustomerField to Media to Partition to Computer to Ram to CPU to Device) They all appear to have common keys so that should not present a problem. Add grouping to your statement to eliminate unnecessary redundancies. At that point, it should just be drag and drop fields onto your report.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply