>>I actually had a situation where I needed a full join. I was woking on year end reporting for our payroll product. I need to create XML that includes State info for each employee. The state section of the XML has tags for both State Income tax and Unemployment Insurance tax. The wage and tax amounts can be different. However, it is possible to pay SIT in one state but not have SUTA... So, for each state the employee working in, when I joined the SUTA records to the SIT records I need a return for each state that they had SIT/SUTA even if they only had one tax or the other, hence the FULL JOIN.
>>
>>BOb
>
>I am not sure I understand correctly. I think you are joining employee with states, twice, for two fields? If that is it, that would be two left joins to avoid omitting employees.

No, after the join I want ONE record for each employee with SIT and SUTA info even if they only have one or the other.

SELECT sit.*,suta.*
FROM taxes sit
FULL JOIN taxes suta ON sit.ee_id=suta.ee_id and sit.state=suta.state
WHERE sit.name = 'SIT' and suta.name = 'SUTA'

So, in the above case they can have SIT records (left table) or SUTA records (right table) or one of each. If I did a left join, I would only get records for employees with SIT only or SIT and SUTA. If I did a right join I would only get employees with SUTA only or with SUTA and SIT. With the full join I get records from SIT and SUTA even if there is no record mathing the join from the 'other' table.

Of course the query is not so simple cause the employees tax table doesn't actually store the state in it, but you should get the idea.

BOb