Re: Complex (for me) table "join" - help please?

Try "SELECT TableA.Category, TableB.PersonID FROM TableA LEFT JOIN TableB ON (TableA.CategoryID = TableB.TableAID) WHERE TableB.ClientID = $cid ORDER BY TableA.Category ASC" LEFT JOIN states that you should return all "TableA," inserting NULL for TableB if there is no match. 'Luck -Szii ----- Original Message ----- From: "Peter Westergaard" <pwestergaard@sympatico.ca> To: <php-db@lists.php.net> Sent: Friday, April 19, 2002 2: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