Query returning too many records

[polynominalc]

Hi

I have written the following sql query below.

SELECT TOP 100 PERCENT dbo.RQREQUIREMENTS.REQUIREMENTPREFIX, dbo.RQREQUIREMENTS.REQUIREMENTNAME,
dbo.RQREQUIREMENTS.REQUIREMENTTEXT, dbo.RQREQUIREMENTS.VERSIONNUMBER,
RQREQUIREMENTS_1.REQUIREMENTPREFIX AS [Traced From], dbo.RQREQUIREMENTHISTORY.VERSIONNUMBER AS [Version History],
dbo.RQREQUIREMENTHISTORY.VERSIONDATETIME, dbo.RQREQUIREMENTHISTORY.VERSIONREASON
FROM dbo.RQREQUIREMENTS RQREQUIREMENTS_1 INNER JOIN
dbo.RQTORELATIONSHIPS ON RQREQUIREMENTS_1.ID = dbo.RQTORELATIONSHIPS.REQUIREMENTID RIGHT OUTER JOIN
dbo.RQREQUIREMENTS ON dbo.RQTORELATIONSHIPS.TOID = dbo.RQREQUIREMENTS.ID FULL OUTER JOIN
dbo.RQREQUIREMENTHISTORY ON dbo.RQREQUIREMENTS.ID = dbo.RQREQUIREMENTHISTORY.ID
WHERE (dbo.RQREQUIREMENTS.REQUIREMENTTYPEID = 51) AND (dbo.RQREQUIREMENTS.REQUIREMENTPREFIX = 'req1501')

The problem is that I keep bringing back three lots of the same change history for the record when I have joined the dbo.RQREQUIREMENTHISTORY table to the query i.e

Version History
1
1
1
2
2
2

Can anybody see what is wrong

Thanks

Mark

 

[SQLSister]

I think we need more information to help.

Give us an example of what is in the tables and what results you expect to get.

 

[SemperFiDownUnda]

this query actually works?

SELECT     TOP 100 PERCENT dbo.RQREQUIREMENTS.REQUIREMENTPREFIX, dbo.RQREQUIREMENTS.REQUIREMENTNAME, 
                      dbo.RQREQUIREMENTS.REQUIREMENTTEXT, dbo.RQREQUIREMENTS.VERSIONNUMBER, 
                      RQREQUIREMENTS_1.REQUIREMENTPREFIX AS [Traced From], dbo.RQREQUIREMENTHISTORY.VERSIONNUMBER AS [Version History], 
                      dbo.RQREQUIREMENTHISTORY.VERSIONDATETIME, dbo.RQREQUIREMENTHISTORY.VERSIONREASON
FROM         [COLOR=#FF0000]dbo.RQREQUIREMENTS RQREQUIREMENTS_1 [/color]INNER JOIN
                      dbo.RQTORELATIONSHIPS ON RQREQUIREMENTS_1.ID = dbo.RQTORELATIONSHIPS.REQUIREMENTID RIGHT OUTER JOIN
                      dbo.RQREQUIREMENTS ON dbo.RQTORELATIONSHIPS.TOID = dbo.RQREQUIREMENTS.ID FULL OUTER JOIN
                      dbo.RQREQUIREMENTHISTORY ON dbo.RQREQUIREMENTS.ID = dbo.RQREQUIREMENTHISTORY.ID
WHERE     (dbo.RQREQUIREMENTS.REQUIREMENTTYPEID = 51) AND (dbo.RQREQUIREMENTS.REQUIREMENTPREFIX = 'req1501')

One thing while I look at this some more.
There is no reason to do a

SELECT TOP 100 PERCENT

That is the default.

Why you are getting multiple rows is most likely because of your data. you base table (from the SQL above which shouldn't even run because you have 2 different table names there, must have multiple matches in one or more of the following tables
RQTORELATIONSHIPS
RQREQUIREMENTS
RQREQUIREMENTHISTORY


Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first