Mysql combine rows based on consecutive key values and then merge

Question

Many thanks for reading ,

I have the following table (view)

#id_internal type  BOOK_ID     Writer       Title     Loan_person_id Loan_Date      Return_Date
    120     1       1002    Writer1         Book1       2            2012-05-21     2012-06-04
    131     0       1002    Writer1         Book1       2            0000-00-00     2012-05-21
    134     0       1002    Writer1         Book1       2            0000-00-00     2012-05-21
    153     1       1002    Writer1         Book1       2            2012-05-21     2012-06-04
    162     0       1002    Writer1         Book1       2            0000-00-00     2012-05-21
    165     1       1002    Writer1         Book1       2            2012-05-21     2012-06-04
    252     1       1012    Writer2         Book2       2            2012-05-23     2012-06-06
    253     0       1012    Writer2         Book2       2            0000-00-00     2012-05-23
    383     1       1012    Writer2         Book2       2            2012-05-23     2012-06-06
    389     0       1012    Writer2         Book2       2            0000-00-00     2012-05-24
    13      1       1008    Writer3         Book3       3            2012-05-20     2012-06-03

created which basically models my small applica!tion for a school library loan log.

I am trying to find out a way to create an sql statement that would do the following: Combine two rows at a time based in the id_internal field (shown in the picture) (first two rows - more two etc until no tuples exist or there is only one at the end) and create a new one with one more column.

What this is all about is that one row of the set (with type = 1) describes the book loan i.e. when the book was loaned and when it was supposed to be returned to the library. The second row (which always has as Loan_Date the time 0000:00:00 and the field type = 0 ) describes the true date the book was returned. The new row will have all the information contained in the first row and as the new column the true return date of the book. There are columns for Book_ID and Loan_person_id (for the reader who borrowed the book) and TYPE with values 0 (return ) and 1 (loan).

From my basic to early intermediate competence in SQL, I fail to use correctly the GROUP BY and GROUP CONCAT. Looking forward for answers or directions.
Many thanks again , Dinos

Edit: The results should be in the form (let us use a view)

Before Processing:
#id_internal type  BOOK_ID     Writer       Title     Loan_person_id Loan_Date      Return_Date
    120     1       1002    Writer1         Book1       2            2012-05-21     2012-06-04
    131     0       1002    Writer1         Book1       2            0000-00-00     2012-05-21
After processing :
Keep one of two id_internal (does not matter):
#id_internal type  BOOK_ID     Writer       Title     Loan_person_id Loan_Date      Return_Date Real_Return_Date
    120     1       1002    Writer1         Book1       2            2012-05-21     2012-06-04     2012-05-21

Many Thanks

I'd recommend posting the problematic SQL. It'll help pinpoint what you're doing wrong better than all this text. — duffymo, Dec 24 '12 at 18:18
Sorry , I was not allowed to post images, as I thought. I used text instead. Many thanks for reading. — Konstantinos Chertouras, Dec 24 '12 at 18:19
Sorry , the sql I tried was very elementary to have any value for the discussion at all. Thanks again. — Konstantinos Chertouras, Dec 24 '12 at 18:23
Please post the result you want so that is is easy to understand — Muhammad Raheel, Dec 24 '12 at 18:24
take a look at this ansewr of mine. This might help. http://stackoverflow.com/questions/11262260/finding-free-blocks-of-time-in-mysql-and-php — Muhammad Raheel, Dec 24 '12 at 18:28
Edit post , to include result wanted. I will look the post as well . Thanks for the prompt replies. — Konstantinos Chertouras, Dec 24 '12 at 18:55

score 0 · Accepted Answer · answered Dec 24 '12 at 18:29

0

I am assuming that you want all book loans (type = 1), so ids 120 and 153 should be separate rows.

The following query takes the minimum loan date that occurs for the book after a given loan:

select t.*,
       (select min(ReturnDate) from t t2 where t.bookid = t2.bookid and t2.id > t.id and t2.type = 0) as ReturnDate
from t
where type = 1

This works for your data because 131 and 134 have the same date. Getting the maximum date before the next loan would be much more complicated in MySQL.

answered Dec 24 '12 at 18:29

Gordon Linoff

1,242,037
58
646
786

It works! Many many thanks (Respect!). I thought it would be much more extended in sql statement lengths. – Konstantinos Chertouras Dec 24 '12 at 19:45
If in a mood drop a line to explain the "from t t2". Is it a join and of what kind? – Konstantinos Chertouras Dec 24 '12 at 20:38
The subquery in the `select` clause is a correlated subquery. The `t2` is an alias used to distinguish between the "t" in the subquery and the "t" in the outer query. The join conditions use both. I'm not a big fan of correlated subqueries in the select clause, but in MySQL that is often the only way to get done what you need done. – Gordon Linoff Dec 24 '12 at 21:13

Mysql combine rows based on consecutive key values and then merge

1 Answers1