How do i write the query for this problem?

Question

Hi I have the details of a quotation in 1 table and the details of revised quotations in another table. In the dropdownlist i want all the quotations and if there are any revisions made to it(in case a single quotation is revised 3 times) i want the latest revised quotation id. If there are more number of quotations revised many times. How do i get all the latest revised quotation ids?

Murugavel S · Answer

Hi Table 1: Quote_id (PK) comments Table 2: Revises_quote_id (PK) quote_id (FK) version_id select * from table1 t1 table t2 where t1.quote_id=t2.quote_id and version_id=(select max(version_id) from table2 t22 where t2.quote_id=t22.quote_id limit 1) Thanks Murugavel S

Jean Paul · Answer

I imagine the following table structures. Quotation Id Amount DateCreated RevisedQuotation Id QuotationId (foreign key) Amount DateCreated DateModified While a Quotation record is selected you can load the related records from the RevisedQuotation table with ORDER BY DESC DateModified (to get the latest modified revised quotation first)