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

Question

Post reply

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

chocthree

Ten Centuries

Points: 1276

May 17, 2019 at 1:51 pm

#3644564

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

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

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

@Taps SSC Eights! Points: 879 More actions · Answer 1

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

chocthree Ten Centuries Points: 1276 More actions · Answer 2

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.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

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?

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

chocthree Ten Centuries Points: 1276 More actions · Answer 4

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 5 years, 7 months ago by chocthree.

rVadim Hall of Fame Points: 3969 More actions · Answer 5

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.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 6

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.

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

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 7

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.