Loading

How do i write the query for this problem?

B M Suchitra

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?

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Murugavel S15y ago
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 Paul15y ago
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)