Basic Join... I think

[Kristie McNickles]

I need help figuring out a problem...

I have two tables:
FIELDS -- (field_id*, agreedname, parentelement)
STATES -- (id*, field_id, state_abbr)

I need to populate a tree with all the possible agreednames (like this):
select agreedname from fields where parentelement = 'AddAdjustments';

I also need to highlight the nodes of this tree that belong to a given state. For instance, if NY is logged in, I want all the nodes to display, but NY nodes will be yellow.

I don't want to run a query within each resultset. Is there a way to do some sort of join that will bring me back all distinct fields in FIELDS (for a given parentelement) and will also bring me some sort of indicator if the STATES table contains an entry with state_abbr = 'NY'?

My join skills suck. I can merely bring back all the fields for the given state, but obviously this is not enough.

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

Any help is appreciated...