Post reply

Inner Join Duplicate Values

  • June 25, 2012 at 3:45 pm

    #145384

    I have Three tables, the PntSourceM_PlySourceM is a view. Table 2 is a Inspecion table with DateTime.

    Table 3 is a Treatment table with DatedTime. When I run the query or view I get duplicate values in the treatment table.

    Date and Times are exact. Their are no duplicate values in the original table.I included the date diff statement

    to see if I could limit the selection and eliminate the duplicates.Didn't work. When a Inspection is done "Background","Event"

    and a "Treatment" are done on the same day.The values in the Point and POly table are all unique, with a one to many

    relationship to the Inspection and Treatment tables. Any Ideas? I have tried Distinct etc. My coding is limited.

    SELECT

    dbo.PNTSOURCEM_PLYSOURCEM.SourceSiteID,

    dbo.PNTSOURCEM_PLYSOURCEM.Habitat,

    dbo.PNTSOURCEM_PLYSOURCEM.SubHabitat,

    dbo.PNTSOURCEM_PLYSOURCEM.OBJECTID,

    sde.POLYNPDESSURVEY.SourceSiteID AS SurvSSID,

    sde.POLYNPDESSURVEY.TreatmentDate,

    sde.POLYNPDESSURVEY.TreatmentBy,

    sde.POLYNPDESSURVEY.SurveyType,

    sde.POLYNPDESSURVEY.FloatDebris,

    sde.POLYNPDESSURVEY.BtmDeposits,

    sde.POLYNPDESSURVEY.Color,

    sde.POLYNPDESSURVEY.Clarity,

    sde.POLYNPDESSURVEY.OilSheen,

    sde.POLYNPDESSURVEY.AquaticLife,

    sde.POLYNPDESSURVEY.Fungi_Slime,

    sde.POLYNPDESSURVEY.SkyCond,

    sde.POLYNPDESSURVEY.Precip,

    sde.POLYNPDESSURVEY.DegreeF,

    sde.POLYNPDESSURVEY.WindSpd,

    sde.POLYNPDESSURVEY.WindD,

    sde.POLYNPDESSURVEY.RHum,

    sde.POLYNPDESSURVEY.Lat,

    sde.POLYNPDESSURVEY.Long,

    sde.POLYTREATMENTMOSQUITO.SourceSiteID AS TreatSSID,

    sde.POLYTREATMENTMOSQUITO.TreatmentDate AS TreatDate,

    sde.POLYTREATMENTMOSQUITO.TreatmentBy AS TreatBy,

    sde.POLYTREATMENTMOSQUITO.Chemical,

    sde.POLYTREATMENTMOSQUITO.MeasurementUnit,

    sde.POLYTREATMENTMOSQUITO.TreatedArea,

    sde.POLYTREATMENTMOSQUITO.ChemicalAmount

    FROM dbo.PNTSOURCEM_PLYSOURCEM INNER JOIN

    sde.POLYNPDESSURVEY ON dbo.PNTSOURCEM_PLYSOURCEM.SourceSiteID = sde.POLYNPDESSURVEY.SourceSiteID INNER JOIN

    sde.POLYTREATMENTMOSQUITO ON sde.POLYNPDESSURVEY.SourceSiteID = sde.POLYTREATMENTMOSQUITO.SourceSiteID

    WHERE (dbo.PNTSOURCEM_PLYSOURCEM.SubHabitat LIKE N'%\WUS')

  • June 26, 2012 at 3:23 am

    #1505462

    How many rows are returned by each of the following queries:

    -- query 1

    SELECT vw.SourceSiteID

    FROM dbo.PNTSOURCEM_PLYSOURCEM vw

    WHERE vw.SubHabitat LIKE N'%\WUS'

    -- query 2

    SELECT vw.SourceSiteID

    FROM dbo.PNTSOURCEM_PLYSOURCEM vw

    INNER JOIN sde.POLYNPDESSURVEY ps ON ps.SourceSiteID = vw.SourceSiteID

    WHERE vw.SubHabitat LIKE N'%\WUS'

    -- query 3

    SELECT vw.SourceSiteID

    FROM dbo.PNTSOURCEM_PLYSOURCEM vw

    INNER JOIN sde.POLYTREATMENTMOSQUITO pm ON pm.SourceSiteID = vw.SourceSiteID

    WHERE vw.SubHabitat LIKE N'%\WUS'

    -- query 4

    SELECT vw.SourceSiteID

    FROM dbo.PNTSOURCEM_PLYSOURCEM vw

    INNER JOIN sde.POLYNPDESSURVEY ps ON ps.SourceSiteID = vw.SourceSiteID

    INNER JOIN sde.POLYTREATMENTMOSQUITO pm ON pm.SourceSiteID = vw.SourceSiteID

    WHERE vw.SubHabitat LIKE N'%\WUS'

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 26, 2012 at 8:25 am

    #1505601

    Thanks for the reply Chris, next time I will color code the sql statement.

    The first query returns 531 rows, second 633, third 247 and the fourth 1454.

    I was thinking if I added the first three query results together I would get the total

    of the fourth. So much for thinking, my biggest downfall.

  • June 26, 2012 at 8:59 am

    #1505626

    croberts 87372 (6/26/2012)

    Thanks for the reply Chris, next time I will color code the sql statement.

    The first query returns 531 rows, second 633, third 247 and the fourth 1454.

    I was thinking if I added the first three query results together I would get the total

    of the fourth. So much for thinking, my biggest downfall.

    You need to know for sure how these tables are supposed to join. Is it just SourceSiteID? INNER or LEFT joins?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 26, 2012 at 9:32 am

    #1505661

    The only common field is the SourceSiteID (GUID).Just a little background to the problem. From the Point or Poly location(PntSourcem_PlySourcem) a survey (PolyNPDESSurvey) can take place. This is signified by an event, "BackGround", "Event", "Post Event". When a event occurs both background and event occurs on the same day followed by a treatment (PolyTreatmentMosquito). I thought of selecting by date, from the datetime field or column, since the events all occur on the same day. Im a little stymied on what approach I should take. Maybe a join is not the way to go, Im in so deep I cant see the forest. Would you like a sample of the tables?

  • June 26, 2012 at 9:36 am

    #1505663

    croberts 87372 (6/26/2012)

    The only common field is the SourceSiteID (GUID).Just a little background to the problem. From the Point or Poly location(PntSourcem_PlySourcem) a survey (PolyNPDESSurvey) can take place. This is signified by an event, "BackGround", "Event", "Post Event". When a event occurs both background and event occurs on the same day followed by a treatment (PolyTreatmentMosquito). I thought of selecting by date, from the datetime field or column, since the events all occur on the same day. Im a little stymied on what approach I should take. Maybe a join is not the way to go, Im in so deep I cant see the forest. Would you like a sample of the tables?

    Some sample data would be awesome! Do you know how to set it up? If not, click on the word "this" in my sig, it will take you to an informative and amusing article. Cheers.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 26, 2012 at 9:43 am

    #1505668

    Would I attach the sample tables or just paste into window?

  • June 26, 2012 at 9:47 am

    #1505671

    Let me hash at it a bit.

  • June 26, 2012 at 9:52 am

    #1505673

    croberts 87372 (6/26/2012)

    Would I attach the sample tables or just paste into window?

    As per the article, CREATE TABLE statements and INSERT INTO statements. The article offers hints on the quickest, easiest methods of generating the INSERT INTO using QUOTENAME.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 26, 2012 at 11:36 am

    #1505753

    Hopefully this will work, if it is in the proper format I will continue with the other tables.

    Please let me know, thanks Chris

  • June 27, 2012 at 1:45 am

    #1505999

    Posters are encouraged to place the DDL and DML directly into a post rather than in an attached file. This looks way too big for that. Does it run?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 27, 2012 at 8:33 am

    #1506175

    Sorry about that, If you could can you remove and throw as far as possible. Its garbage. I will get back to you as soon as I can get my %$^%& together

  • June 27, 2012 at 5:13 pm

    #1506417

    I jumped the gun a little bit and am refining the selection tables and had to get permission to upload sample data. The target has changed as usual, but the problem still remains the same. FYI the end result is to have the table created and then queried from Excel into a pre-existing format which I have no control over (format). I think I won't have time to respond by the weekend and am taking off for a week , hopefully we can pick up were we left off. I appreciate your patience. Thanks

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply