469,948 Members | 2,889 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,948 developers. It's quick & easy.

Help getting multiple listings

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

Aug 12 '05 #1
2 1244
AK
not enough information, but anyway: have you tried DISTINCT keyword?

Aug 12 '05 #2
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)
Aug 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Neil Trigger | last post: by
4 posts views Thread by John Pether (john | last post: by
reply views Thread by Rudi Ahlers | last post: by
3 posts views Thread by Keith Chadwick | last post: by
185 posts views Thread by jacob navia | last post: by
reply views Thread by richard12345 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.