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" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
