Parents and children; a link story

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Parents and children; a link story

Author

Topic

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-07 : 10:57:32

okay, so lets make a hypethetical.

I have product alpha, and it has variations

A, ei, and firstletrox

i need a list of product alpha, A, ei, and firstletrox with certain info

for example

productname, size

Alpha, 12

A, null

ei, 8

firstletrox, null

the problem is, I can't have null values, and in all cases where the value is null, the 'truth' is that it inherets its values from the parent (in this ex. Alpha. So how do i say

if data is null then take the parents info

????

I am working with SELECT SQL.

I will give a bounty of 37 internets to whomever can solve this problem.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-07 : 11:01:22

Is this the entire table structure? How do we know Alpha is the parent of the other three? Any way to find?

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-07 : 11:06:27

inside the dataset there is no way to know that alpha has children, but the children do know who their parent is.

there is a spot that says "is parent of" from which I can pull the dataname "alpha". The data is also aware that it is a child or not.

so my code will ultimatly look like

when thedata is where ischildofparent is true then $!use data from!$ 'is parent of'

is that clear? I'm trying to tell as much without going into the whole database structure.

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-07 : 11:07:05

so to be clear, children know they are children, and know who their parent is.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-07 : 11:34:31

since the exact structure and sample data is not known, it could be something like this...

declare @t table (id int, productname varchar(20), [size] int, childof int)
insert @t
select 1,'Alpha', 12,null union all
select 2,'A', null,1 union all
select 3,'ei', 8,1 union all
select 4,'firstletrox', null,1 union all
select 5,'Beta', 10,null union all
select 6,'B', null,5 union all
select 7,'fg', 8,5 union all
select 8,'lastletrox', null,5

update t
set t.[size] = case when t.[size] is null then t1.[size] else t.[size] end
from @t t
inner join (select * from @t where childof is null)  t1 on t.childof = t1.id

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-07 : 11:37:50

quote:
Originally posted by ConradK

so to be clear, children know they are children, and know who their parent is.

Not always....like in a commune...who really knows what's going on

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-07 : 11:41:40

quote:
Originally posted by ConradK
the 'truth' is that it...

quote:

Indiana Jones: Being a DBA (Archaeology) is the search for fact... not truth. If it's truth you're looking for, Dr. Tyree's philosophy class is right down the hall.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-07 : 11:50:54

I kindof get it.

So what is this 'update' thing?

so when you guys make such big answers, the reason you are doing it, is because I could actually run that exact code and get the info i want. You are building the code itself.

I'm learning so much, i really am!

so what I need is to run an update. function...

I'm sure what you have works; and I'll try to run something here... but not too sure whats going on exactly...

gimme 30 minutes to bang my head against it.

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-07 : 14:52:23

I tried update syntax.

Is this right?

Select

Products.ProductCode AS "productcode"
,options.OptionCatID as "variation theme"

Update options

set options.optioncatid = case when patindex(options.OptionCatID, OptionCategories.id) >0 then optionCategories.OptionCategoriesDesc end

FROM products, optioncategories, options LEFT JOIN Options_ApplyTo ON Options.ID = Options_ApplyTo.OptionID

where patindex(products.productcode, Options_ApplyTo.ProductCode) > 0

order by products.productcode

it keeps spitting me out for various reasons...

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-07 : 15:11:11

We can't give you the actual update statement until we know what exactly you are trying to do..you need to provide table structure and sample data at the very least...but...

The Update syntax should be something like this..

Update A
set A.optioncatid = case when <something> then optionCategories.OptionCategoriesDesc else <something> end
FROM options A 
INNER JOIN products B ON A.<something> = B.<something>
INNER JOIN optioncategories C ON A.<something> = C.<something>
LEFT JOIN Options_ApplyTo ON Options.ID = Options_ApplyTo.OptionID
where <somecondition> > 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources