Create a Column if child table has attribute

General

Create a Column if child table has attribute

Hello; I am working with an Account Table and a Transactional Table.  The Account table unique ID is [ACCT].[CASE_ID]; there is only one record for each [CASE_ID].  I am connecting to the transactional table [TRANS] using [ACCT].[CASE_ID] = [TRANS].[CASE_ID] in a LEFT JOIN.  Within the transactional table, one unique case can have multiple transactions; so it is your typical waterfall type relationship. 

 

There is a field within the transactional table called [TYPE].  Every single transaction within the transaction table either has a value in the [TYPE] field or a NULL. 

 

Now I am only pulling information from the ACCT table; so for each unique CASE_ID there is only one record.  What I am looking to do is to create a column in my main query to say...Look at each CASE_ID within the transaction table; if any of the transactions for said CASE_ID have a value in the [TYPE] column (or is not null); set the value to "Y". 

6 REPLIES

Re: Create a Column if child table has attribute

Hi,

 

I think your basic requirement can be met using the following:

 

SELECT A.Case_ID
   ,(CASE 
     WHEN T.Type IS NOT NULL THEN 'Y' 
     END)
,... other columns as required... FROM Account AS A LEFT OUTER JOIN Trans AS T ON A.Case_ID = T.Case_ID

Does that help?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: Create a Column if child table has attribute

Dave,

Just to be clear, lets say a CASE_ID has 5 transactions. 

 - If all 5 transactions are NULL; this will show as "N",

 - If all 5 transactions are NOT NULL; this will show as "Y",

 - If 3 of 5 transactions are NOT NULL; this will show as "Y"

 

Are all three scenerios correct?  Basically what I am asking is; if even one transactions is not null; I want my new column to give me a "Y".  If I use your logic and scenerio 3 that I listed; my main query will give me two records for that single CASE_ID; one with a Y and one with a N in my results... I only want one record per CASE_ID.

Re: Create a Column if child table has attribute

Hi,

 

Yes your understanding is correct.

 

One minor change to the SQL (to generate a 'N' value):

ELECT A.Case_ID
   ,(CASE 
     WHEN T.Type IS NOT NULL THEN 'Y' 
     ELSE 'N'
     END)
   ,... other columns as required...
FROM Account AS A
LEFT OUTER JOIN Trans AS T
  ON A.Case_ID = T.Case_ID

Remember that (using your example where the CASE_ID has 5 transactions), this CASE_ID will be in your answer set 5 times - because the row from the Account table joins to 5 rows from the Trans table.

 

If you only want the Account row to appear once in the ouput then you need to do some aggregation, something like:

ELECT A.Case_ID
   ,MAX(CASE 
     WHEN T.Type IS NOT NULL THEN 'Y' 
     ELSE 'N' 
     END)
   ,... other columns as required...
FROM Account AS A
LEFT OUTER JOIN Trans AS T
  ON A.Case_ID = T.Case_ID

GROUP BY...

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: Create a Column if child table has attribute

Right now, before I tried to implement this new column, I am only getting one record per CASE_ID, becuase even though I am connected to the TRANS table, I do not have any output rows from the TRANS table.  So my SELECT DISTINCT is allowing for one record per CASE_ID.  But when I implement the new column as you state above; it is not adding a up to two records per CASE_ID, because each CASE_ID can have a "Y" only, a "N" only, or one of each. 

 

What I want my output to show is... if the CASE_ID has ALL NULLS, give me "N", if there is even one transactions with a NOT NULL in the [TYPE] field, give me one record with a "Y", not one with a "Y" and one with a "N".

 

Am I explaining this ok?

Re: Create a Column if child table has attribute

Hi,

 

How about showing some sample data and what you want the output to be?

 

I don't thik using DISTINCT is going to give you the correct answer.

 

I think you can do this with MAX and a CASE expression. as per my previous suggestion:

 

SELECT A.Case_ID
   ,MAX(CASE 
     WHEN T.Type IS NOT NULL THEN 'Y' 
     ELSE 'N' 
     END) AS IND_COL
FROM Account AS A
LEFT OUTER JOIN Trans AS T
  ON A.Case_ID = T.Case_ID

GROUP BY 1;

This will give you one row per Case_ID value, regardless of the number of transaction rows.

 

This one row will contain a value of 'N' for IND_COL if all Trans rows (for the Case_ID) contain NULL in the 'Type' column, otherwise it will contain 'Y'.

 

If that isn't what you want then I think some sample data and required output will help.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: Create a Column if child table has attribute

Perfect; adding the MAX will do the trick, becuase if one CASE_ID has a Y and a N; the MAX will equal the Y only!

 

Thnak you!