Re: Basic Join... I think

[sneakyimp]

the query you printed has an implicit INNER JOIN (meaning only records with some kind of match in both tables will be returned).

your WHERE clause limits the result to records from fields that have matching records in state_info where the state_abbr is 'NY'. so no other states will show.

i'm a bit concerned that you said you're building a tree...this usually implies a '3d' structure...meaning it can be tough to fetch all the tree contents in one go. your query hints though that you really only want one branch of the tree...the elements whose parent id is AddAdjustments.

If that's correct, your query should be enough to fetch those nodes. Whether or not a node should be yellow or not isn't really up to the query, it's something to be handled by the code that translates the query results -- ASP or PHP or actionscript or whatever. You'll just need to add state_abbr to the select part of your query and remove the requirement that all fetched items have state_abbr=NY

select
a.field_id,
a.agreedname
b.state_abbr
from
fields a,
state_info b
where
a.parentelement = 'AddAdjustments' and
a.field_id = b.field_id;