Join Three Tables

[schafjoe]

dbo.GeirsAdd (
Primekey int IDENTITY (1, 1) NOT NULL ,
ReportNumber varchar (25) NULL ,
Platform int NOT NULL ,
Contact varchar (30) NULL ,
Sqe_Se varchar (30) NULL
)

dbo.GeirsImpact (
Primekey int IDENTITY (1, 1) NOT NULL ,
ReportNumber int NOT NULL ,
ImpactDate datetime NULL ,
Impact text NULL
)

dbo.GeirsStatus (
Primekey int IDENTITY (1, 1) NOT NULL ,
ReportNumber int NOT NULL ,
StatusDate datetime NULL ,
Status text NULL
)

dbo.GeirsImpact.ReportNumber relates to dbo.GeirsAdd.Primekey
dbo.GeirsStatus.ReportNumber relates to dbo.GeirsAdd.Primekey

dbo.GeirsAdd has one record
dbo.GeirsImpact has four records that match the dbo.GeirsAdd
dbo.GeirsStatus has five records that match the dbo.GeirsAdd

What I want it to do is return a table that has five rows in it.

The following Query gets the results but it puts it in 20 rows.

Select dbo.GeirsImpact.Primekey as GeirsImpactPrimekey,
dbo.GeirsImpact.ImpactDate,
dbo.GeirsStatus.Primekey as GeirsStatusPrimekey,
dbo.GeirsStatus.StatusDate,
dbo.GeirsAdd.Primekey as GeirsAddPrimekey,
dbo.GeirsAdd.ReportNumber,
dbo.GeirsAdd.Contact,
dbo.GeirsAdd.Sqe_Se,
dbo.GeirsAdd.Platform,
dbo.GeirsStatus.Status,
dbo.GeirsImpact.Impact
FROM dbo.GeirsAdd
LEFT JOIN dbo.GeirsImpact on dbo.GeirsImpact.ReportNumber = dbo.GeirsAdd.Primekey
LEFT JOIN dbo.GeirsStatus on dbo.GeirsStatus.ReportNumber = dbo.GeirsAdd.Primekey
WHERE (dbo.GeirsAdd.ReportNumber = '212002224730')

 

[sunila7]

Hi,

did u try

Select dbo.GeirsImpact.Primekey as GeirsImpactPrimekey,
dbo.GeirsImpact.ImpactDate,
dbo.GeirsStatus.Primekey as GeirsStatusPrimekey,
dbo.GeirsStatus.StatusDate,
dbo.GeirsAdd.Primekey as GeirsAddPrimekey,
dbo.GeirsAdd.ReportNumber,
dbo.GeirsAdd.Contact,
dbo.GeirsAdd.Sqe_Se,
dbo.GeirsAdd.Platform,
dbo.GeirsStatus.Status,
dbo.GeirsImpact.Impact
FROM dbo.GeirsAdd
LEFT JOIN dbo.GeirsImpact on dbo.GeirsImpact.ReportNumber = dbo.GeirsAdd.Primekey
INNER JOIN dbo.GeirsStatus on dbo.GeirsStatus.ReportNumber = dbo.GeirsAdd.Primekey
WHERE (dbo.GeirsAdd.ReportNumber = '212002224730')


Sunil