Help getting multiple listings

[sjoshi]

I'm trying this simple query but getting multiple listings for
change_number field

SELECT c.Change_Number, c.Submission_Date, c.Short_Description,
c.CurrentStatus, dlv.Name, s.Description, kw.Subcategory
FROM Changes c
INNER JOIN Deliverables dlv ON c.Deliverable = dlv.DlvID
INNER JOIN Keywords kw ON c.Deliverable_Keyword = kw.KywID
INNER JOIN Status s ON c.CurrentStatus = s.Status
WHERE (c.Application = 3) AND (c.ChangeType = 'CR')
AND (dlv.Name = 'Common Route') AND (c.ProductGroup = 1)
ORDER BY c.Submission_Date DESC

73239 3/7/2005 Publish HasReportableParts 100 Common
Route Open-Filed TEF Integration
73239 3/7/2005 Publish HasReportableParts 100 Common
Route Open-Filed TEF Integration
73239 3/7/2005 Publish HasReportableParts 100 Common
Route Open-Filed TEF Integration
73239 3/7/2005 Publish HasReportableParts 100 Common
Route Open-Filed TEF Integration

Any ideas what might be wrong here ?

thanks
Sunit

[AK]

not enough information, but anyway: have you tried DISTINCT keyword?

[Hugo Kornelis]

On 12 Aug 2005 09:46:10 -0700, sjoshi wrote:

I'm trying this simple query but getting multiple listings for
change_number field

SELECT c.Change_Number, c.Submission_Date, c.Short_Description,
c.CurrentStatus, dlv.Name, s.Description, kw.Subcategory
FROM Changes c
INNER JOIN Deliverables dlv ON c.Deliverable = dlv.DlvID
INNER JOIN Keywords kw ON c.Deliverable_Keyword = kw.KywID
INNER JOIN Status s ON c.CurrentStatus = s.Status
WHERE (c.Application = 3) AND (c.ChangeType = 'CR')
AND (dlv.Name = 'Common Route') AND (c.ProductGroup = 1)
ORDER BY c.Submission_Date DESC
(snip)
Any ideas what might be wrong here ?

Hi Sunit,

That means that you have more than one matching row in at least one of
the joined tables. Copy and paste the code below into Query Analyzer to
see how rows can be duplicated in a join.

To solve it, we need to know more about your data, tables and
requirements. See www.aspfaq.com/5006.
CREATE TABLE Test1 (A int NOT NULL PRIMARY KEY,
B int NOT NULL)
CREATE TABLE Test2 (C int NOT NULL PRIMARY KEY,
D int NOT NULL)
INSERT INTO Test1 (A, B)
SELECT 1, 1
UNION ALL
SELECT 2, 1
INSERT INTO Test2 (C, D)
SELECT 1, 1
UNION ALL
SELECT 2, 1
UNION ALL
SELECT 3, 2

-- Show what's in the tables
SELECT A, B FROM Test1
SELECT C, D FROM Test2

-- Do a join - some row match two other rwos
SELECT Test1.A, Test1.B
, Test2.C, Test2.D
FROM Test1
INNER JOIN Test2 ON Test2.D = Test1.A

-- Same join - but this time, hide some of the columns
-- now we really appear to ahve duplicates!
SELECT Test1.A, Test1.B
-- , Test2.C, Test2.D
FROM Test1
INNER JOIN Test2 ON Test2.D = Test1.A

-- Done - clean up the used tables
DROP TABLE Test1
DROP TABLE Test2
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)