Joined Tables

[violette]

here's my code;

SELECT b.alert_id,cur.stock_code, current_name,d.board_name,cur.year_id, cur.period_id,cur.fixed_assets, 
prev.year_id AS prev_year_id,prev.period_id AS prev_period_id, prev.fixed_assets AS prev_fixed_assets,
(cur.fixed_assets*100/prev.fixed_assets) AS "total1", c.para1
FROM company_financial AS cur
LEFT JOIN company_info a ON (a.stock_code=cur.stock_code)
LEFT JOIN company_alert b ON (b.year_id=cur.year_id)
LEFT JOIN ref_alert_parameter c ON (c.alert_id=b.alert_id)
LEFT JOIN ref_board d ON (d.board_code=a.board_code)
LEFT JOIN ref_sector e ON (e.sector_code=a.sector_code)
LEFT JOIN ref_sub_sector f ON (f.sub_sector_code=a.sub_sector_code)
LEFT OUTER JOIN company_financial AS PREV ON cur.year_id = prev.year_id + 1
ORDER BY year_id

after joining all the required tables, the output display redundant data. few data are repeated. can anyone tell me why and how can i fix it. thanks.

[debasisdas]

What is the table structure.

[adam_k]

The result of your query is a cartesian product. This means that every record from each dataset is matched with every record from the other datasets.
You need to add criteria to your joins until you get a unique 1 to 1 match for your records.

[Pgmer]

Yes Left join will give the cartesian product. Try using inner join. it all depends on the table structure and their relationship defined as said by debasis.

[adam_k]

@Pgmer: Usually I agree with your posts, but this time is an exception.
A left join won't cause anything different than an inner join in terms of cartesian product. The cartesian isn't determined if all records of left table should be included in the results or if they should only be included if there is a matching record in the right table.
Cartesian products are caused (accidentally) when not enough keys to produce a one to one relationship between the tables have not been provided.