MySQL Forums
Forum List  »  Optimizer & Parser

Re: data design optimization
Posted by: BattleMan BattleClan
Date: April 01, 2006 06:38AM

it shows that my design lack great deal of clarity and sense.

what i was trying to do is to seperate the sets into two categories
1. the main group category [identified by an activityid=0]
2. the activies of the certain group [activityid<>0]

the above query finds all of the activities which their corresponding activity happens within a certain date range.

that is there are two parts for the query
SELECT * FROM BudgetActivities WHERE
ActivityGroupID IN (
SELECT ActivityGroupID FROM BudgetActivities WHERE
ActivityID = 0
PeriodStart < _PeriodEnd
PeriodEnd > _PeriodStart
) ORDER BY ActivityGroupID,ActivityID;

the inner part is
\SELECT ActivityGroupID FROM BudgetActivities WHERE
ActivityID = 0
PeriodStart < _PeriodEnd
PeriodEnd > _PeriodStart

which selects all of the groups [identified by their activitygroupid] which their main item [identified by activityid=0] happens in a certain date range

the second part [the complete query] selects all of the data sets which belong to any of the groups found in the first part

i can only assume that my design is so flawed that it is beyond description

i assume i need to split the data into two tables

to make things simpler i'll describe my needs
i need a
1. group dataset. with "Name" and "Date Range"[period start and period end]
2. activity dataset. with "Name" and other properties but with a link to the "group dataset"

my need is to select activities which belong to groups that answer certain criterias [in this example, data range]

i thank you alot for your help. it is much apriciated.

Options: ReplyQuote

Written By
March 30, 2006 07:44AM
March 30, 2006 09:02AM
March 30, 2006 01:43PM
March 30, 2006 04:03PM
March 31, 2006 04:59PM
Re: data design optimization
April 01, 2006 06:38AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.