How do i write this query, with left join. since the framework i use doesn't support right join i need to rewrite the query. Can any one suggest me a possible solution.
select Audit.history_id,Audit.field,modifiedtime,operation from Audit right join (select History.history_id from History where refid=2000000020088 order by
modifiedtime limit 5) as Hist on Audit.history_id=Hist.history_id;
desc Audit
+------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------+------+-----+---------+-------+
| AUDIT_ID | bigint(19) | | PRI | 0 | |
| HISTORY_ID | bigint(19) | | MUL | 0 | |
| FIELD | varchar(50) | | | |
| OLD_VALUE | varchar(50)| YES | | NULL | |
| NEW_VALUE | varchar(50)| YES | | NULL | |
+------------------+------------+------+-----+---------+-------+
desc History
+---------------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------------------+-------+
| HISTORY_ID | bigint(19) | | PRI | 0 | |
| REFID | bigint(19) | | MUL | 0 | |
| OPERATION | varchar(50) | YES | | NULL | |
| MODIFIED_TIME | datetime | | | 0000-00-00 00:00:00 | |
+---------------+-------------+------+-----+---------------------+-------+
Simply switch the relations for a left join:
In practice, explicit right outer joins are rarely used, since they can always be replaced with left outer joins (with the table order switched) and provide no additional functionality.
Source:Wikipedia.org