Post reply

NEED help please. Join query to a FK. The FK table join is on nullable field

  • October 4, 2016 at 7:34 pm

    #321871

    Ok I am having a brain fart or something. I have written a system that merges one DB to another. Both same structure. All the queries are created dynamically; very cool actually.

    I have a table that has a nullable field. This field has a FK relationship to another table. Most of the records have a value and it's all good. It looks like I am missing 12 records in my merge because this field is NULL. I have tried LEFT JOIN, isNULL. I can not get these records.

    Below is the query. Let me give a little back story so you can put your head around it.

    I have a Source DB. and I have a target DB.

    In my DB (Where the project resides) I have what I call a Correlation table. So let's say I have 1,000 records in my target. I have 1,000 records in my source. In the source 500 of those records are already in the target. Due to constraints I don't move those. I do populate the keys in the Correlation table. So in this case let's say PKID is 1, in both DBs. Well in correlation I will have both the SourceID and DestinationID as value 1.

    Now for the 500 records the do merge. Let us assume that it is the first 500 records in the Source that are merging to the target. Well the First record has an ID of 1, but when hitting target (Sine The PK is identity then this record that used to be a 1, well he is ID 500 now.

    In Correlation I will have SourceID as 1 and DestinationID as 501 (First 500 match remember).

    This is working like a charm. So the query below. (and there are 4 dynamic statements created per table so this is not the only statement). I have a join.

    My table that I am merging data to is called (Assignment) Assignment has a column called

    (PerformanceConfigChoiceID ) This column has a FK relationship back to the table ConfigChoice. CongfigChoice has a column called ConfigChoiceID. PerformanceConfigChoiceID is nullable and it is the records where this field is NULL that I am having an issue.

    Can someone please help out on this one?

    Thank you in advance. And this has to be done in T-SQL so this is the only option no SSIS or outside app.

    SELECT st.[AgencyGUID], st.[AssignmentGUID], st.[AssignmentTypeConfigSystemChoiceID], st.[ConfigTransactionSplitPlanGUID], st.[CustomerMarkupGUID], st.[DateEntered], st.[EmployeeGUID], st.[EndReasonConfigChoiceID], st.[ExternalDB], st.[ExternalID], st.[Friday], st.[IsW2], st.[Monday], st.[Note], FK1.DestinationID, st.[Saturday], st.[StaffingOrderGUID], st.[StaffingSupplierSiteGUID], st.[Sunday], st.[Thursday], st.[Tuesday], st.[UserGUID], st.[Wednesday], st.AssignmentID, st.StaffingOrderGUID

    FROM [addb20].[Source_MergeAMD3_WithData].[dbo].[Assignment] st

    LEFT JOIN [addb20].[Source_MergeAMD3_WithData].[dbo].[ConfigChoice] as FK1Table on FK1Table.ConfigChoiceID = st.PerformanceConfigChoiceID

    LEFT JOIN [ETL].[dbo].[ETLCorrelation] FK1 on FK1.SourceID = ST.PerformanceConfigChoiceID

    AND FK1.SourceTable = 'ConfigChoice'

    AND FK1.BatchID = 5902

    AND FK1.SourceID <> FK1.DestinationID

    WHERE st.[AssignmentGUID] NOT IN (SELECT [AssignmentGUID]

    FROM [Target_MergeAMD4_WithData].[dbo].[Assignment])

    <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

  • October 5, 2016 at 5:53 am

    #1904866

    What is the purpose of the LEFT JOIN to addb20.Source_MergeAMD3_WithData.dbo.ConfigChoice?

    I ask because that table is not referenced anywhere else in the query.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • October 5, 2016 at 7:09 am

    #1904892

    -- Collect the 12 values of ST.PerformanceConfigChoiceID

    -- for the rows where FK1.DestinationID is NULL.

    -- Probe the ETLCorrelation table for those rows:

    SELECT FK1.SourceTable, FK1.BatchID, FK1.SourceID

    FROM [ETL].[dbo].[ETLCorrelation] FK1

    WHERE FK1.SourceID IN (<< your list of ST.PerformanceConfigChoiceID here >>)

    “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

Viewing 3 posts - 1 through 2 (of 2 total)

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