Is there a better (shorter) way to right this short query?

  • Hi,

    I have been given a piece of SQL to use to create a dimension table, however the SQL is referencing itself. I'm wondering if the left join to itself is required. Is there a better (shorter) way to write the below:

    Thanks in advance.

    SELECT *
    FROM [RawDownloads].[dbo].tbl_K8_Customer C
    LEFT JOIN
    (
    SELECT
    CreditCheckLevel
    , Customer
    FROM
    [RawDownloads].[dbo].tbl_k8_Customer
    WHERE
    CreditCheckLevel = CustomerID
    ) AS CCL
    ON C.CreditCheckLevel = CCL.CreditCheckLevel
  • Well you can do this - makes it short not sure if actually better

    SELECT c.* , CCL.column_names
    FROM [RawDownloads].[dbo].tbl_K8_Customer C
    LEFT JOIN [RawDownloads].[dbo].tbl_k8_Customer CCL
    ON C.CreditCheckLevel = CCL.CreditCheckLevel
    AND C.CreditCheckLevel = CCL.CustomerID

     

  • Thanks for your reply.

    Do you need to do a left join at all. Couldn't you simply use a where clause?  I'm just trying to get my head around if a left join is required.

  • This is a bizarre piece of code – can you explain what it is intended to do, please?

    Why would CustomerId ever be equal to CreditCheckLevel? Even if it is, why is this case of particular interest?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • I'm afraid I'm not privileged to that info.

    Customer ID has differing values, all similar to A123 or C883 (etc..). CreditCheckLevel either has the matching CustomerID for that row - hence the lookup between the two values, or a completely different value in which it will have a NULL when used in the LEFT JOIN.

    The odd thing is, the actual CustomerID value used in the returned dataset (dimension) is from the same table which is the left sided table of the LEFT JOIN.

    I'm just wondering what this query does exactly, and is a LEFT JOIN needed?  I imagine you gurus could decipher it and answer my question. Thanks.

    • This reply was modified 1 year, 9 months ago by  chocthree.
  • LEFT JOIN will return all rows from table C and matching rows from sub query CCL. Unmatched rows on CCL side will be filled with NULLs. Total number of rows should be equal to number of rows in C.

    Whether or not this is desired behavior is for you to decide.

    --Vadim R.

  • Total number of rows should be equal to number of rows in C.

    Unless multiple occurrences of the same CreditCheckLevel are returned by the subquery, in which case there will be more rows in the final resultset than exist in [RawDownloads].[dbo].tbl_K8_Customer.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Does your boss also expect you to write code without any DDL? Perhaps is time for you to update your resume and move on to a company that is run by rational people. We also have no idea what your data model is. But we do know that the use of the “TBL_” prefix is a design flaw called “tibble” because it mixes data and metadata in a single name. I also see you like to use select* in code; while this is syntactically valid, it is incredibly bad programming.

    Why is a K8_customer_id a totally different attribute in a separate column from a mere customer_id? Why is a credit_check_level the same as a customer_id? See why we need DDL.

     

    Please post DDL and follow ANSI/ISO standards when asking for help. 

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

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