Query repeating field

Posted on 2005-05-10
Medium Priority
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
Anthony Perkins earned 1000 total points
ID: 13974308
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

Brendt Hess earned 1000 total points
ID: 13974495
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.

Author Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

840 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