I've never used the join operator before, but I can't seem to get my tables joined correctly, so I think its time:

Three tables (appoint_detail, address, table_add_detail (joined twice?))

Appoint_detail has key, facility_code, unique_id
Address has lookup_name, userdef_1
table_add_detail has unique_id, field_name, field_value
(BTW--I didn't define this database!)

Appoint_detail looks like this:
key facilitycode unique_id
32  XYZ GROUP    1
32  ABC GROUP    2
23  DEF GROUP    3
23  ABC GROUP    4

Unique ID is unique to the line (integer key), key is the file number the user uses.

address looks like this:
lookup_name    userdef_1
XYZ GROUP      45
ABC GROUP      99
DEF GROUP      88

Table_add_detail looks like:

Unique fieldname  fieldvalue
1      padmis     70
1      adpriv     Y
2      padmis     80
3      adpriv     Y

Ok, I want out of that:

key    facility_code   unique_id    userdef_1   padmis   adpriv
32     XYZ GROUP       1            45          70       Y
32     ABC GROUP       2            99          80       (blank)

I've been fussing with the sql syntax all afternoon. If someone has a idea, I'd be grateful to hear it! If you want to see my best efforts, ask!

Thanks!