Peter, I've you tried doing a inner join, left outer join, right outer join?
Advertising
What db program are you using? Could you post what your current select statement looks like? Mike ----- Original Message ----- From: "Peter Westergaard" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, April 19, 2002 5:46 PM Subject: [PHP-DB] Complex (for me) table "join" - help please? > I'm trying to perform a "join" that seems like it should be simple, but I'm just ending up in a morass. Hopefully someone who's standing a step or two back from the problem can help me solve this one... > > What I have: > > Table A (Category): with a 3-tier category structure: > -Nature > -Category > -Sub-Category > > Table B (Responsibility): with a reference to Table A, and a client id, and the id of a person responsible for that Sub-category for that client. > -TableA-id > -Client-id > -Person-id > > So, what I want is a simple select which will fetch for me all rows in (Category) with the relevant person from (Responsibility) if there is one for a specific Client, but with a blank in that column if there isn't one. > > ie. (Category) has 10 entries (let's call them Ca-Cj), and (Responsibility) has 5 entries for Client 1 (Ca,Cb,Cc,Cd,Ce) and 3 entries for Client 2 (Cd,Ce,Cf). > > When I run the select for Client 1, I'd like to see... > Ca - person > Cb - person > Cc - person > Cd - person > Ce - person > Cf - > Cg - > Ch - > Ci - > Cj - > > And when I run the same select for Client 2 I'd like to see: > > Ca - > Cb - > Cc - > Cd - person > Ce - person > Cf - person > Cg - > Ch - > Ci - > Cj - > > I tried a basic "outer join" type select, and of course for Client 1 I miss Cf, and when I use it for Client 2 I miss Ca-Cc. > > Anyone able to sort me out here? > > I need to use a basic Select (instead of building a temp table or using variables in a PHP script) because another database process that I don't control needs to do the same thing. > > Think it's possible, or am I going to have to go and insert records into "Responsibility" for every entry in Category for every Client, resulting in (#Categories x #Clients) rows? (The latter idea sure doesn't make me happy, but it would be 'easier' to build, if not to maintain. (And, as you can appreciate, since I'm not the one maintaining it, I'm like THAT CLOSE from just going that way and saying 'screw em'!) > > Hopefully, > -Peter Westergaard > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.330 / Virus Database: 184 - Release Date: 2/28/02 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
