CODEWITHSUNDEEP
sql
CallumVass
CallumVass

Reputation: 11448

SQL - Using a Subselect

I have a table with 3 rows: AccountNum, InvoiceAcc and Blocked. Most rows my table have an InvoiceAcc but not all. AccountNum is the unique identifier and its possible for a number of AccountNum's to have the same InvoiceAcc. Blocked is an int value varying from 0, 1 and 2.

The InvoiceAcc is based off of an AccountNum so think of it as a Parent Office which then has proceeding Child Offices.

For example, a parent office like so:

AccountNum - 1, InvoiceAcc - null, Blocked - 2 might have a child offices like so: 

AccountNum - 1-1, InvoiceAcc - 1, Blocked - 0

AccountNum - 1-2, InvoiceAcc - 1, Blocked - 1

What I'd like to do is check the InvoiceAcc of a row and its subsequent AccountNum blocked value. So in my example above, If I did a query like so:

SELECT BLOCKED FROM CustTable WHERE AccountNum = '1-1'

It would return 0 as that is the blocked value. What I'd like it to do is return 2 as that has the higher value and its the parent account. Also, if the parent acc has a blocked value of 1 and the child account which I'm querying has an blocked value of 2, then it should return 2. Im assuming a subselect would be needed here?

It should return the highest blocked value based on either itself or its parents account, if it has a parent account

Upvotes: 0

Views: 80

Answers (3)

SMK
SMK

Reputation: 2158

Use this : 

select AccountNum ,(select count(*) from CustTable  where AccountNum like t.AccountNum +'-%') as Totalsubacc from CustTable t where AccountNum not like '%-%'

Upvotes: 0

GavinCattell
GavinCattell

Reputation: 3963

You can add a write your query like this as only as you only have one level (e.g. parent -> child)

SELECT CASE 
  WHEN child.blocked > COALESCE(parent.blocked,0)
  THEN child.blocked 
  ELSE parent.blocked 
END as blocked
FROM CustTable child 
LEFT JOIN CustTable parent ON child.InvoiceAcc = parent.AccountNum
WHERE child.AccountNum = '1-1'

Upvotes: 1

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

That's a bit messy. It's the "1-?" that's messing you up.

ParentAccount = 1, SubAccount = 2, would resolve that

Then you could select by ParentAccount with somethimng like

Select parent.Blocked From SomeTable parent
inner join SomeTable Children On Parent.ParentAccount = Child.ParentAccount

Keep your current structure and you are going to have to come up with a bunch of string functions to extract the A from "A-C"

Upvotes: 0

Related Questions