May 17, 2019 at 1:51 pm
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
May 17, 2019 at 2:30 pm
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
May 17, 2019 at 2:35 pm
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.
May 17, 2019 at 2:36 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 17, 2019 at 2:45 pm
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.
May 17, 2019 at 3:04 pm
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.
May 17, 2019 at 3:13 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 17, 2019 at 7:35 pm
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply