Union with another subquery…can't join the two together

[catherineadams]

I have a table Rent_Payment with subclasses checks and like_value (PK is payment_ID). Each of the subclasses have a description (ex: like_value has 'Fixed a car'). Tenant pays the Rent Payment (linked by FK tenant_ID). I want to display:
Tenant, and description from the subclass of how they paid. I unioned all the subclasses but I don't understand how to use the union and make sure that the rent_payment.tenant is the same as tenant.tenant_ID

select tenant.fn, tenant.ln, rent_payment.AMOUNT,checks.check_desc, direct_deposit.DEPOSIT_DESC, like_value.description
  from tenant, rent_payment, checks, direct_deposit, like_value
    where rent_payment.tenant_ID = tenant.tenant_ID AND
 
select rent_payment.payment_ID
  from rent_payment, checks
  where rent_payment.payment_ID = checks.payment_ID
   UNION
select rent_payment.payment_ID
  from rent_payment, direct_deposit
      where rent_payment.payment_ID = direct_deposit.payment_ID
    UNION
select rent_payment.payment_ID
  from rent_payment, like_value
  where rent_payment.payment_ID = like_value.payment_ID;

The union all works on its own and the top part (up until the AND) all works on its own too…its just how to put them together I am having an issue with.

[catherineadams]

Sorry its my first post! Not sure how to do it!

[Rabbit]

Instructions on how to use the forum are in the FAQ.

If you want to pull data from those 3 tables: checks, direct_deposit, like_value. Then you need to outer join to those tables.

If you don't know what an outer join is, that is explained in the SQL tutorial located here: http://www.w3schools.com/sql/sql_join_left.asp