well... I'm not 100% sure of your table structure, but if the children are in same tables, then join to each:
select * from action_items a, child_table b, child_table c where a.owner_id = b.person_id and a.creator_id = c.person_id now if the values in the action_items table could be null (not assigned yet), then you'll want to do an outer join: select * from action_items left join child_table as a on action_items.owner_id = a.person_id left join child_table as b on action_items.creator_id = b.person_id [not 100% sure on my left join syntax... double check with the docs, or the many posts to this list...] > -----Original Message----- > From: Brent Baisley [mailto:[EMAIL PROTECTED] > Sent: Friday, November 21, 2003 3:06 PM > To: [EMAIL PROTECTED] > Subject: Suggestions on querying parent/child records > > > I'm trying to figure out the best approach for doing this > query. I have > a list of action items, typically about 50, that I need to display. > Each action item will have two related sets of child items (two > different groups of people). So I need to query three main > databases, a > parent and two children. > > One obvious approach would be to query the action items and then run > two queries for each action item to pull the two separate > related child > items. Then just join then in php for output. But that would always > mean 2*actions+1 queries every time the page would be viewed. > That's a > lot of database connections. > > Another approach is to use a join query on the parent and one child. > Then run a query for each action to pull the other child data. Again > joining the data together in php for output. But this means a lot of > redundant parent data due to the one to many join. > > I'm sure there would be a third approach to do it all in one > query with > lots of redundant data, but I haven't sat down with my brain > yet (it's > out to lunch) to talk about that approach. I'm thinking this > one may be > the best for performance even though a lot more data would need to be > transfered. > > Any other ideas or suggestions on optimizing these approaches? Thanks. > > -- > Brent Baisley > Systems Architect > Landover Associates, Inc. > Search & Advisory Services for Advanced Technology Environments > p: 212.759.6400/800.759.0577 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
