I have two tables namely , Customer_invoice , customer_comments . customer_invoice contains the invoice no and customer_id and customer_comments has comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id. I know this is an poor design , but now we cannot change the structure of customer_comments table.
Data is as below
Invoice_id Customer_id date
1 101 01/01/2014
1 102 01/01/2014
1 103 01/01/2014
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
1 1 hello1 101 102 NULL 01/01/2014
2 1 hello12 101 102 NULL 02/01/2014
3 1 hello1 23 101 102 NULL 01/01/2014
4 1 hello123 4 101 102 NULL 02/01/2014
The way we insert the data in customer_comments table is , initially when we select customer1_id , and then add an comment , the first column that is customer1_id is populated and the comment_desc is stored with the comment date . The same thing follows for the second comment added for customer_id=101. But when we add another comment for customer_id=102 , for the same invoice number , then the customer2_id is populated and its comment details is stored.
I need the below output:
Invoice_no , comment_date , customer_id
1 02/01/2014 101
1 02/01/2014 102
i need to get the latest comment for an invoice no , added by an customer.