SQL Query against 3 tables with some column names the same

See more:

SQL

Oracle

I have 3 tables that I need to query from. I am trying to get a monthly breakdown of costs. I have a rough outline of some of the columns in the table below.

cost_type - this lists the type of costs and the category id
cat_type
cat_id

recur_cost
recur_id
lease_id
property_id
amount_expense
cat_id
salesTax
description
date_start
date_end
etc...

adj_cost
adjust_id
lease_id
property_id
amount_expense
cat_id
salesTax
description
due_date
etc...

please note that although these tables have some similar columns they dont have the same number of columns

I am trying to do a select against the tables where the lease_id and property_id are the parameters...because the calculations are dif. this is confusing me

i.e if its a recurring cost I have to take the amount_expense and divide it by 12 to get monthly, where as the adjustment table needs no calculation.

So for when I do a select using table.property_id and table.lease_id I am getting either way too many results or it is jumbling them together. If a cost has a recur_id, then it shouldn't have an adj_id, but for some reason it join them even tho they should be dif. rows...

also, the cost_cat table is being joined on the cat_id, but if it's an adjustment then it would need to join on that, otherwise it would join on the recur_cost.cat_id

I need the column names to stay amount_expense, sales_tx, ls_id etc when I get the results because I will copying this into <datasource> that I will be using to create a <panel type="grid">

I am not in front of my work computer or I could copy my sql code, but its pretty much a mess right now.

ANY IDEAS? I believe the issue is also when I call in lease_id and property_id, I have to say WHERE (recur_cost.lease_id= ANDrecur_cost.property_id =) OR (adj_cost.lease_id= AND adj_cost.property_id =)
and it is connecting the results...

Basically..there is more to the query, but these are the majors...
a SELECT statement from the two table

Case
WHEN
recur_cost
THEN
amount_expense/12
ELSE
adj_cost.amount_expense
END
as montly_cost,
recur_id.recur_id,
adj_cost.adj_id,

cost_cat.cat_type as cost_category

(here it is given the cat_id from which ever table the current row is and returning the string meaning of what that category id means),

Case
WHEN
recur_cost
THEN
recur_cost.description
ELSE
adj_cost.description
END
as description,
recur_id.recur_id,
adj_cost.adj_id,
JOIN
cost_cat on cat_id(from whichever table the current row is)
WHERE (recur_cost.lease_id= AND recur_cost.property_id =) OR (adj_cost.lease_id= AND adj_cost.property_id =)