Self-join 1

[pwills]

Scenario:
1. I have a table of customers (id, company, parent)
2. One of the columns is "parent" which can be another id.
3. I would like a VIEW of customers that shows the parent company if the parent field contains data, otherwise it shows the child company (i.e. parent overrides child)

E.g. table is:
Bob, <null>, Bill
Bill, Microsoft, <null>

So view should show

Bob, Microsoft
Bill, Microsoft

A really clever solution would be one that recurses up the tree, but actually it is not necessary as I only anticipate one level.

 

[MeanGreen]

Try this:

select t1.id,case when t1.company is null then t2.company else t1.company end
from customers t1 left join customers t2
on t1.parent = t2.id


Hope this helps.

 

[pwills]

Seems very nearly the one I need. Except that won't the left join only show one row?

 

[MeanGreen]

Nope. You need the left join to show all rows. One other note, you have to be sure that the parent is unique or you will get some strange results.

Hope this helps.

 

[pwills]

Can I check something with you?

If I always pick the parent data id a parent ID is provided (i.e. regardless of whether null or not), is the query then:

select t1.id, t2.company
from customers t1 left join customers t2
on t1.parent = t2.id

Obviously <null> parent is permitted but any value other than an existing id is not. Do you know how I would constrain the parent field like this?

 

[MeanGreen]

You would want to use a trigger probably. Try this:

create trigger tr_parent on customers
for Insert,Delete
as
if update(parent) and (select isnull(parent,'0') from inserted)<> '0'
BEGIN
if not exists (select t1.id
from inserted t1 left join customers t2
on t1.parent = t2.id where t2.company is not null)
BEGIN
Print 'PARENT DOES NOT EXIST'
rollback transaction
END
END


Hope this helps.

 

[pwills]

Hi MeanGreen

The basic query, without the case, doesn't seem to work. Take the following table:

id, company, parent_id
Bill,Microsoft,<null>
Bob,<null>,Ben
Ben,Oracle,<null>

My query needs to produce the inherited company name:

Bill, Microsoft
Bob, Oracle
Ben, Oracle

The query
select t1.id, t2.company
from customers t1 left join customers t2
on t1.parent_id = t2.id

produces

Bill, <null>
Bob, Oracle
Ben, <null>

 

[MeanGreen]

That is correct, you have to have the case statement. The case statment is what populates your NULL values with the proper company.

Hope this helps.

 

[MeanGreen]

Here is another way to run the query without the case statement:

select t1.id,coalesce(t1.company,t2.company) as company
from customers t1 left join customers t2
on t1.parent = t2.id

Hope this helps too.

 

[pwills]

Thanks, I also found that the following works:

select t1.id,t2.company
from customers t1 left join customers t2
on isnull(t1.parent,t1.id) = t2.id

Pretty neat but may not be ANSI