Re: Query problem ...

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Keith Adler (KeithAdler_at_discussions.microsoft.com)
Date: 09/10/04

  • Next message: Michael Rys [MSFT]: "Re: Query problem ..." 
    Date: Fri, 10 Sep 2004 16:09:01 -0700

    The problem is that i have many joins on this query, and each join results in
    slightly more data being created. Some of the joined tables have n records.
    It's a mess because I cannot figure out how to predict how the XML AUTO will
    layout the elements. Unfortunately, because I'm using BizTalk 2004 I cannot
    use XML EXPLICIT.

    "Michael Rys [MSFT]" wrote:

    > The duplication on the leaf nodes indicate that you get two rows in your SQL
    > query.
    >
    > Can you rewrite the SQL part of the query to only result in one row (e.g.,
    > using distinct)? Or would that remove duplicates that you want to have
    > exposed?
    >
    > Thanks
    > Michael
    >
    > "Keith Adler" <KeithAdler@discussions.microsoft.com> wrote in message
    > news:9B608829-A601-44C8-B361-FA51530561BB@microsoft.com...
    > > I'm creating a query that has a number of joins and I'm getting duplicate
    > > values displayed nested as follows:
    > >
    > > <Root>
    > > <Paint StorePaintID="1">
    > > <Brush StoreBrushID="10001" StoreBrokerageID="113"
    > > receivedDate="2004-01-26T00:00:00" EstimatedValue="1.2">
    > > <GlueLocation GlueLocationID="5" GlueLocation="New York, NY"
    > > StoreType1="Housekeeping" StoreType2="Housekeeping"
    > > StoreType3="Structured Product">
    > > <CurrencyType CurrencyType="U.S. Dollar">
    > > <Storywriter StorywriterPersonID="283">
    > > <Artist ArtistPersonID="810">
    > > <Tree TreeID="175">
    > > <TreeType TreeDescription="RC" TreeCode="AB">
    > > <ProductType ProductID="16" ProductDescription="Professional
    > > Liability - Financial Institution D&O"
    > > ProductCode="FDO">
    > > <StoreUnit StoreUnit="1" LaHouseBrushStatus="Type A"
    > > LaHouseBrushDate="2004-04-14T16:13:05.873"
    > > LaHouseBrushStatusID="4795">
    > > <Comments CommentText="sdagsadgsda"
    > > commentDate="2004-01-26T00:00:00">
    > > <House RBIStatusID="1926" statusDate="2004-02-19T00:00:00"
    > > description="Red" />
    > > <House RBIStatusID="1926" statusDate="2004-02-19T00:00:00"
    > > description="Red" />
    > > <House RBIStatusID="4795" statusDate="2004-04-14T16:13:05.873"
    > > description="Green" />
    > > <House RBIStatusID="4795" statusDate="2004-04-14T16:13:05.873"
    > > description="Green" />
    > > </Comments>
    > > </StoreUnit>
    > > </ProductType>
    > > </TreeType>
    > > </Tree>
    > > </Artist>
    > > </Storywriter>
    > > </CurrencyType>
    > > </GlueLocation>
    > > </Brush>
    > > </Paint>
    > > <Paint StorePaintID="2">
    > > <Brush StoreBrushID="10002" StoreBrokerageID="113"
    > > receivedDate="2004-01-26T00:00:00" effectiveDate="2004-03-31T00:00:00"
    > > EstimatedValue="3.1">
    > > <GlueLocation GlueLocationID="5" GlueLocation="New York, NY"
    > > StoreType1="Housekeeping" StoreType2="Structured Product"
    > > StoreType3="Structured Product">
    > > <CurrencyType CurrencyType="Yen">
    > > <Storywriter StorywriterPersonID="746">
    > > <Artist ArtistPersonID="810">
    > > <Tree TreeID="176">
    > > <TreeType TreeDescription="T1" TreeCode="CA">
    > > <ProductType ProductID="9" ProductDescription="Professional
    > > Liability - Commercial D&O" ProductCode="CDO">
    > > <StoreUnit StoreUnit="1" LaHouseBrushStatus="Typce B"
    > > LaHouseBrushDate="2004-03-24T08:45:56.107"
    > > LaHouseBrushStatusID="3249">
    > > <Comments CommentText="g" commentDate="2004-01-26T00:00:00">
    > > <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
    > > description="Red" />
    > > </Comments>
    > > <Comments CommentText="asfsdaf"
    > > commentDate="2004-03-08T13:54:25.200">
    > > <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
    > > description="Red" />
    > > </Comments>
    > > <Comments CommentText="asdfasdg"
    > > commentDate="2004-03-19T13:59:07.450">
    > > <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
    > > description="Red" />
    > > </Comments>
    > > <Comments CommentText="sdagasdg"
    > > commentDate="2004-03-24T13:47:11.733">
    > > <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
    > > description="Red" />
    > > </Comments>
    > > <Comments CommentText="asgasg"
    > > commentDate="2004-03-26T13:16:44.797">
    > > <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
    > > description="Red" />
    > > </Comments>
    > > <Comments CommentText="asdgasd"
    > > commentDate="2004-03-26T14:52:12.607">
    > > <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
    > > description="Red" />
    > > </Comments>
    > > <Comments CommentText="gasdgasd"
    > > commentDate="2004-03-29T12:18:06.480">
    > > <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
    > > description="Red" />
    > > </Comments>
    > > <Comments CommentText="gasdgasd"
    > > commentDate="2004-03-31T10:15:17.513">
    > > <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
    > > description="Red" />
    > > </Comments>
    > > <Comments CommentText="asdgsdag" commentDate="2004-01-26T00:00:00">
    > > <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
    > > description="Red" />
    > > </Comments>
    > > <Comments CommentText="sadgsadgsadgsad"
    > > commentDate="2004-03-08T13:54:25.200">
    > > <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
    > > description="Red" />
    > > </Comments>
    > > <Comments CommentText="gasdg"
    > > commentDate="2004-03-19T13:59:07.450">
    > > <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
    > > description="Red" />
    > > </Comments>
    > > <Comments CommentText="sdgasdg"
    > > commentDate="2004-03-24T13:47:11.733">
    > > <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
    > > description="Red" />
    > > </Comments>
    > > <Comments CommentText="asg" commentDate="2004-03-26T13:16:44.797">
    > > <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
    > > description="Red" />
    > > </Comments>
    > > <Comments CommentText="asdgasdgsadg"
    > > commentDate="2004-03-26T14:52:12.607">
    > > <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
    > > description="Red" />
    > > </Comments>
    > > <Comments CommentText="sadgs"
    > > commentDate="2004-03-29T12:18:06.480">
    > > <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
    > > description="Red" />
    > > </Comments>
    > > <Comments CommentText="adgsadgsdag"
    > > commentDate="2004-03-31T10:15:17.513">
    > > <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
    > > description="Red" />
    > > </Comments>
    > > </StoreUnit>
    > > </ProductType>
    > > </TreeType>
    > > </Tree>
    > > </Artist>
    > > </Storywriter>
    > > </CurrencyType>
    > > </GlueLocation>
    > > </Brush>
    > > </Paint>
    > > </Root>
    > >
    > > since I am using Biztalk I must use FOR XML AUTO for the query, could
    > > someone please explain how I can get rid of the duplication of values?
    >
    >
    >

  • Next message: Michael Rys [MSFT]: "Re: Query problem ..."

    Relevant Pages

    • Re: Deleting duplicate records/same table
      ... append the tblUniqueRecords fields into the fields on tblMaster. ... The new table without the duplicates is called ... Prepared By is the type of query I was ...
      (microsoft.public.access.queries)
    • Re: Query to delete duplicate records - but NOT original
      ... > unique records - I've already got an update query to grab the unique ... > hsc but one other field different - it ignores those 2. ... but this method of removing duplicates requires it. ... >> Access Database Samples: www.rogersaccesslibrary.com ...
      (microsoft.public.access.queries)
    • RE: Want to edit a table based on a query of a query of several qu
      ... First create union query like this -- ... Use that query in design view and add all of the other nine tables. ... This puts all the data in to a single query output without duplicates. ... Then I run a big query of all 9 queries to consolidate the data into one big ...
      (microsoft.public.access.queries)
    • Re: correct way to write the syntax for a Dlookup in a query expression in VB
      ... those that return false when the query runs to another table and leave ...    c) that you rewrite your sql select as a join checking for nulls ... DLookup; I am not). ... the duplicates may include differing information in particular ...
      (comp.databases.ms-access)
    • Re: Problem with delete duplicates query
      ... You might look at the Help topic called "Delete a group of records with a query". ... the final query to actually delete the records (the query based on the table and the query identifying the duplicates), I get a message that "Could not delete from specified tables". ... and then attach an Autonumber field to serve as the primary key and delete using that. ... FROM Table1 AS T1 ...
      (microsoft.public.access.queries)