.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How to remove duplicates from query results caused by table joins

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :

It's been a while since I used SQL and I'm coming across a problem where by the database design is adding a lot of useless output to a query I need to run and I'm not very clever so don't know how to remove the crap I don't need.

This is what I'm up against:

Create Table #Planning(Assy nvarchar(255), PartNo nvarchar(255))
Create Table #PlanningCosts(Assy nvarchar(255), MaterialCost float, LabourHours float, LabourCost float)
Create Table #PlanningLabour(Assy nvarchar(255), OpNo Nvarchar(255), OpDesc nvarchar(255), OpTime float)
Create Table #PlanningMaterial(Assy nvarchar(255), Material nvarchar(255), MaterialQty float)

View Complete Post

More Related Resource Links

Query a Table with Japanese Characters returns always 0 results


Hi all, I wonder if you could give me a hand on this please...

I've a table (lets say with 2 columns: ID int, Vendor Name nvarchar(255)) with some records with Japanese Characters. 

When I try to query that table for the specific rows with Japanese string I always get 0 results.


Table example:


ID Vendor Name

1 Bruno Ferreira

2 ??????????


Query Example:

select * from myTbl where [Vendor Name] collate <<collations name>> = '??????????'


Result always nothing...


I've tried with all the possible available collations but with no luck

collations I've tried:











lookup table query


we are doing our cms migration. both cms's have their own articleID/contentID, we have a lookup table that contain the article URL and its content id.  Now we want to query that table on every single click to get its respective id. 

How and where i can implement this?

How To Remove a Query Parameter from a URL


for a string such as this:


how would I remove the parameter id 12?


Linq query results to List collection



I have a GridView in my page. I want to use below code in code beind. query nothwing DB using linq . add linq query result to new list and then bind list as gridview Datasource

I use below code . But I don't know how to add results to List? Could any one tell me how !

Public Class OrderCity
        Private _name As String
        Private _city As String
        Public Property name As String
                Return _name
            End Get
            Set(ByVal value As String)
                _name = value

            End Set
        End Property
        Public Property city As String
                Return _city
            End Get
            Set(ByVal value As String)
                _city = value

            End Set
        End Property
    End Class
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim dbe As New SFFAV1DataContext
        Dim latestadsDs = From p In dbe.Products _
                            Select New With {p.Title, p.ProducedCity} _
                             Take (2)

        Dim FilteredList As New List(Of OrderCity)()
        For Each result In latestadsDs
            Dim Menedfcou = Nothing
            FilteredList.Insert(0, Menedfcou)

LINQ query with multiple joins, problem


I am using a LINQ query with multiple joins, the last join does not return any values even though values exist in the database. Below is my code.

when the query returns suiteNameTrg and SuiteTypeTrg are empty, all other values are returned correctly.

string suiteNameTrg = string.Empty;
            string suiteTypeTrg = string.Empty;
            using (DataClassesDataContext db = new DataClassesDataContext())
                    var productQuery = from assets in db.ASSETs
                    join relocatableUnits in db.RELOCATABLE_UNITs on assets.RUID equals relocatableUnits.RUID into assets_units
                    from relocatableUnits in assets_units.DefaultIfEmpty()
                   join build in db.BUILDINGs on assets.BUILDING_ID equals build.BUILDING_ID into assets_bins
                   from build in assets_bins.DefaultIfEmpty()
                   join test in db.TEST_SUITEs on assets.TEST_SUITE_ID equals test.TEST_SUITE_ID into test_bins
                   from test in test_bins.DefaultIfEmpty()
                    join testTrgt in db.TEST_SUITEs on assets.TARGET_TEST_SUITE_ID equals testTrgt.TEST_SUITE_ID into testTrgt_bins
                    from testTrgt in testTrgt_bins.DefaultIfEmpty()

                    select new

Create Table from MDX query

Hi,I want create a table from the result of the MDX query. When I finish the wizard of import data in SQL Server Environment. at the last stage, the error is : Pre-execute (Error) Messages Information 0x4004300b: Data Flow Task 1: "component "Destination - Query" (31)" wrote 0 rows.(SQL Server Import and Export Wizard) please guid  me.Thank you a lot.Naeimeh

Remove the lines from the query output

On TSQL Command of; set nocount onselect @@version Getting the output as;                                                                                                                                                                                                                                                                  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)  May  3 2005 23:18:38  Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) How to remove the lines from the output?

Application to Constantly query Table Values and Refresh with most recent value

Hello, I must apologize in advance because I am not sure if this is the correct place for this question. I am looking for ideas on how to build an application that in the most basic sense contains a field with the most current data from a table in a SQL Server database (this would be based off of a datetime timestamp in the table). I am new to this kind of problem and am unsure where to start looking for solution ideas. The table value updates variably and could possibly update every second to every hour. I would like to design an application that would be lightweight to both the server and the user's machine. This would take the place of an unneeded report on my Reporting Services server. Any and all suggestions would be welcome because I am not sure of options I even have on this. Thanks in advance for your time and consideration, Matt

Transact-sql - tricky query to get a list of people from a table where i belong too.

I've no idea how to manage and optimize this query: I've four table. - User (userId, name) - Member (memberId, name, ownerId) The ownerId = user.userId - PresentPeople (activityId, presentId, ...) so presentId = memberId - Activity (activityId, ownerId, startDate...) and ownerId = user.userId So here for an user, I need members created by this user, and for this members I need the list of presentPeople for the activity that theyjoined. But I don't know how to get this list because they are in the list. Not sure if i'm clear there, Big help would be very helpfull :)) thanks

remove Duplicates

Hi, I am want to create a primamry key on column a in table FARA, I am receiving an error that I have duplicates in column a. I tried copying the table using : select distinct into table FARA-u from table FARA. thought this should remove duplicates but I am still getting same error when i try to set primamry key on column a. how can i remove those duplicates ( I wil need to remove the whole row that conatins duplicates on column a) Thanks!

Using joins for this query, is it possible

I was wodnering whether its possible to use joins for this weird query, the subquery as i understand isnt the best for performance.   SELECT c.PersonURN, c.CDRValue, sm.CDRValue AS [Customer Value], r.CDRValue AS [Supplier Value] FROM tblProfile c left join tblCustomerCDRLookup sm on sm.PersonURN = c.PersonURN AND sm.PriceMatchCode = (select vc.PriceMatchCode from tblPricePlan vc where vc.PlanName='VOIP') left join tblSupplierCDRLookup r on r.PersonURN = c.PersonURN AND r.PriceMatchCode = (select vc.PriceMatchCode from tblPricePlan vc where vc.PlanName='VOIP EXTRA') AND r.AddressURN = c.AddressURN

E-mail query results

I have the following piece of code that returns nothing when there is only one row that fits the criteria. If there are two or more rows that match the criteria it works. Any idea what is wrong?? Declare @drugID varchar(4000),@lineresult varchar(4000), @SEND VARCHAR(256), @RECP VARCHAR(256), @TITLE VARCHAR(256), @BODY VARCHAR(4000), @SERVERNAME VARCHAR(256) set @drugID = '' declare r_cursor INSENSITIVE CURSOR FOR SELECT AHI_DRUG_DOSE.Drug_Display_Name+ ' ' +convert(varchar(10),AHI_CAB_EVENT.Station_Id)+ ' ' +convert(varchar(25),AHI_CAB_EVENT.Event_Dttm)+ ' ' + AHI_CAB_EVENT.Event_Type+ ' ' + convert(varchar(10),AHI_CAB_EVENT.Trans_Qty)+ ' ' +convert(varchar(25),AHI_CAB_EVENT.Site_Patient_Id)+ ' ' +AHI_CAB_EVENT.Pat_Name+ ' ' +AHI_CAB_EVENT.Misc1 FROM AHI_DRUG_DOSE AHI_DRUG_DOSE INNER JOIN CRX_DATA.dbo.AHI_CAB_EVENT AHI_CAB_EVENT ON AHI_DRUG_DOSE.Drug_Dose_Id = AHI_CAB_EVENT.Drug_Dose_Id WHERE (AHI_CAB_EVENT."Station_Id" = 'OPS' OR AHI_CAB_EVENT."Station_Id" = 'OR1' OR AHI_CAB_EVENT."Station_Id" = 'OR2' OR AHI_CAB_EVENT."Station_Id" = 'PACU') AND AHI_CAB_EVENT."Event_Type" = 'WASTE' AND AHI_CAB_EVENT."Misc1" <> 'Partial Dose' AND AHI_CAB_EVENT."Event_Dttm" >= get

Search query returning 0 results

I'm using a query which returns 0 results but was working some weeks ago.  I checked the code and nothing has changed since then.  I created a Search Center Site on another port and using the same terms I get many results there.  I checked my scopes and they are all right. Nothing changed there either. Could someone help me figure out what may be the problem in my query.  Or maybe it's my Scopes/Content Sources configuration. SELECT URL, FileName, Title, Description, Rank, Size, HitHighlightedSummary, Path FROM SCOPE() WHERE freetext(defaultproperties, ' +légumes')  AND ( "scope" = 'Scope - My Portal - All Site - fr'  )    ORDER BY Rank DESCDaniel Siconnelli There is neither good nor evil... only mogwais and gremlins

sql server query get a list of a table

Hi guys, I've a hard query to manage ... I need to get for a report the number of required people to an appointment, but this query works but just for the regarding person related to the appointment. SELECT CRMAF_FilteredContact.OwnerID ,CRMAF_FilteredContact.OwnerIDname ,SU.businessunitidname ,SU.eu_reporthubname -- ,SU.eu_reportcountryidname ,CRMAF_FilteredContact.ContactID ,CRMAF_FilteredContact.FullName ,CRMAF_FilteredContact.invoke_tiername ,CRMAF_FilteredAppointment.ActivityID ,CRMAF_FilteredAppointment.RegardingObjectID ,CRMAF_FilteredAppointment.Subject ,AP.ActivityPartyID ,AP.PartyID ,case when CRMAF_FilteredAppointment.ActivityID is null then null else CRMAF_FilteredContact.ContactID end as s_contactsvisited -- # of contactsvisited FROM FilteredContact CRMAF_FilteredContact JOIN FilteredSystemUser SU ON CRMAF_FilteredContact.ownerid = SU.SystemUserID <strong> LEFT JOIN FilteredActivityParty AP ON ( AP.PartyID = CRMAF_FilteredContact.ContactID or AP.PartyID = CRMAF_FilteredContact.OriginatingLeadId) AND AP.participationtypemask = '8' -- regarding</strong> LEFT JOIN FilteredAppointment CRMAF_FilteredAppointment ON AP.ActivityID = CRMAF_FilteredAppointment.ActivityID AND CRMAF_FilteredAppointment.ownerid = SU.SystemUserID AND CRMAF_FilteredAppointment.stat

T-SQL 2005 table query

Hi, I have SQL 2005 table like below one declare @OrderTable TABLE (OrderID varchar(10),OrderValue decimal(10,2),OrderDate DateTime) INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',455.85,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',866.45,'2010-01-25') INSERT INTO @OrderTable VALUES('P06',749.61,'2010-01-28') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-02-23') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-02-23') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-03-23') Final result I am looking for is SET NULL value in SELECT query Whenever Same OrderID and OrderDate is found... OrderID  OrderValue   OrderDate P06         25.22   2010-01-24 P06        NULL             2010-01-24 P06      NULL             2010-01-24 P06        NULL             2010-01-24 P06      NULL              2010-01-24 P06 &

Sql Server 2005 ( remove header information from query output file "Job 'bustd_Daily_Query_Output_1

Dear All,I have scheduled job for querying output & asve it into particuler folder....but in the top of file i getting the message like "Job 'bustd_Daily_Query_Output_13July09' : Step 1, 'Query1' : Began Executing 2009-07-13 16:32:06"I want to remove this & want output in txt file format without above header....RegardsRavendra Cindia

Tricky SELECT query from a Single Table

Hi I have a 2 rows of data in a table as mentioned below   Table1             ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 NULL 40 NULL 20 4 9 2010 400 NULL 200 NULL  I need a SELECT query to get the output in a single row as ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 400 40 200 20 Could anybody help me in writing a query for this? Thanks
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend