MySQL Forums
Forum List  »  Optimizer & Parser

Multiple join and Where clause
Posted by: michele terlizzi
Date: November 15, 2006 05:13AM

I have 2 tables:

table1:
CREATE TABLE `testdb`.`table1` (
`isin` int(11) NOT NULL default '0',
`description` varchar(100) default NULL,
PRIMARY KEY (`isin`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


table2:
CREATE TABLE `testdb`.`table2` (
`isin` int(11) default NULL,
`rating_type` enum('expected','final') default NULL,
`id` int(10) unsigned NOT NULL,
`date` datetime NOT NULL,
`rating` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `FK_table2_1` (`isin`),
KEY `Index_3` (`rating_type`,`isin`),
CONSTRAINT `FK_table2_1` FOREIGN KEY (`isin`) REFERENCES `table1` (`isin`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



I would produce a report like this:
-----------------------------------------------------------------------------
isin | final rating | expected rating | date final | date expected |
-----------------------------------------------------------------------------
a null 1 null 2000-01-01
b 1 1 2001-01-01 2001-02-01
c 1 null 1 null


I found that trying to filter the select by the fields of table2 the select doesn't extract all the rows I supposed to extract...
----------------------------------------------------------
select
t1.isin, t2.rating, t3.rating, t2.date, t3.date
from
table1 t1
left join table2 t2 on (t2.isin = t1.isin)
left join table2 t3 on (t3.isin = t1.isin)
where
t3.rating_type = 'expected'
and t2.rating_type = 'final'
and t2.date > '2000-01-01'
----------------------------------------------------------

I solved the problem creating 2 different views and using them instead of
t2 and t3 aliased table:

v_fianl_rating: select * from table2 where rating_type = 'final';
v_expected_rating: select * from table2 where rating_type = 'expected';

Have you some solution instaed of create views?
thanks
bye bye

Options: ReplyQuote


Subject
Views
Written By
Posted
Multiple join and Where clause
42750
November 15, 2006 05:13AM
6855
November 22, 2006 04:20PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.