Query repeating field

Posted on 2005-05-10
Last Modified: 2010-03-19
Why is this query repeating the case field multiple times for the same caseID?

select distinct c.swDateCreated, c.swCaseID, cu.swName, c.swsubject, v.vtName, cv.vtVersion, c.vtInProduction, c.swos from sw_Case c INNER JOIN vt_Component v ON c.vtComponentID = v.vtComponentID
INNER JOIN sw_Customer cu ON cu.swCustomerID = c.swCustomerID
INNER JOIN vt_Component_Version cv ON cv.vtComponentID = v.vtComponentID
WHERE c.swDateCreated >= '1/1/2005' and c.swDateCreated <= '3/31/2005'
order by c.swDateCreated, cu.swNaME
Question by:dba123
    LVL 75

    Assisted Solution

    by:Anthony Perkins
    Probably because one or more of the other fields in the Select statement is DISTINCT.  But without seeing some sample data we can only speculate.
    LVL 32

    Accepted Solution

    select distinct c.swDateCreated, c.swCaseID, cu.swName, c.swsubject, v.vtName, cv.vtVersion, c.vtInProduction, c.swos

    This will produce only one row for each CASE value if, and only if, there is only one matching record in each of the other tables (vt_Component, sw_Customer, vt_Component_Version) for each CASE record.  I doubt this is the case.

    If you want to limit it to only one row per CASE value, you must decide what single value from the other tables to display.  If you need to show multiple values (such as all component names for a case), then you will have multiple rows per Case value.

    Remember:  SELECT DISTINCT means that every column in the row must be the same for the subsequent rows to not be shown.
    LVL 1

    Author Comment

    figured it out, my linnking of IDs was screwed up.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now