CODEWITHSUNDEEP
phpmysqljoin
Edward
Edward

Reputation: 77

How to get duplicate rows but with different dates

My Table

| ID |   Date   | Next Date | 
| 1  |2014-12-23| 2014-12-25|
| 2  |2014-12-20| 2014-12-22|
| 3  |2014-12-19| 2014-12-21|
| 4  |2014-12-15| 2014-12-18|

How can I show duplicates when searching within date range in my search result query:

select * from my_table where date >= '2014-12-14' AND date <='2015-01-22' ORDER BY (date) ASC

My Results are:

1. ID: 4, Date: 2014-12-15, Next Date: 2014-12-18
2. ID: 3, Date: 2014-12-19, Next Date: 2014-12-21
3. ID: 2, Date: 2014-12-20, Next Date: 2014-12-22
4. ID: 1, Date: 2014-12-23, Next Date: 2014-12-25

What I want to do is show duplicates with date, next date and sorted by date, next date.

1. ID: 4, Date: *2014-12-15*
2. ID: 4, Next Date: *2014-12-18*
3. ID: 3, Date: *2014-12-19*
4. ID: 2, Date: *2014-12-20*
5. ID: 3, Next Date: *2014-12-21*
6. ID: 2, Next Date: *2014-12-22*
7. ID: 1, Date: *2014-12-23*
8. ID: 1, Next Date: *2014-12-25*

What would be a good solution for my situations?

Thanks

Upvotes: 0

Views: 64

Answers (1)

Super-intelligent Shade
Super-intelligent Shade

Reputation: 6449

I think this is what you want:

select "ID", "Order Date"
from
(
    (select "ID", "Date", "Next Date", "Date" as "Order Date" from Table1)
    union all
    (select "ID", "Date", "Next Date", "Next Date" as "Order Date" from Table1)
) as Table2
order by "Order Date"

NB: Change Table1 to the actual name of your table.

Upvotes: 1

Related Questions