Join Table - Rows to Column

Question

I need help for querying two table become a table

The first table, Tbl_Project:

|No_Project     | Date_Start| Date_End  |
|---------------|---------- |-----------|
|050/1/2015     |   20150105| 20150315  |
|051/2/2015     |   20150205| 20150515  |
|052/3/2015     |   20150305| 20150715  |
|053/4/2015     |   20150405| 20150915  |

The second table, Tbl_DimDate:

| Date       | Year  |  Month| Day   | Tanggal_Id | Weekth |Month_Name |  
| -----------| ------| ------| ------| -----------| ------ |-----------|
| 2015-01-05 | 2015  |      1|    5  |    20150105|     2  |    Jan    |  
| 2015-02-05 | 2015  |      2|    5  |    20150205|     6  |    Feb    |  
| 2015-03-05 | 2015  |      3|    5  |    20150305|    10  |    Mar    |  
| 2015-04-05 | 2015  |      4|    5  |    20150405|    15  |    Apr    |  
| 2015-03-15 | 2015  |      3|   15  |    20150315|    12  |    Mar    |  
| 2015-05-15 | 2015  |      5|   15  |    20150515|    20  |    May    |  
| 2015-07-15 | 2015  |      7|   15  |    20150715|    29  |    Jul    |  
| 2015-09-15 | 2015  |      9|   15  |    20150915|    38  |    Sept   |

How to make join table look like this:

|No_Project | Date_Start| Date_End  | Week_Start | Week_End |
|-----------|---------- |-----------|------------|----------|
|050/1/2015 |   20150105| 20150315  |        2   |      12  |
|051/2/2015 |   20150205| 20150515  |        6   |      20  |
|052/3/2015 |   20150305| 20150715  |       10   |      29  |
|053/4/2015 |   20150405| 20150915  |       15   |      38  |

Possible duplicate of [SQL JOIN and different types of JOINs](http://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) — MikaelF, Mar 03 '17 at 04:34

Masoud · Accepted Answer · 2017-03-02T23:11:51.493

-1

You can try something similar to this:

SELECT `project`.*, `dimdate1`.`Weekth` as `Week_Start`, `dimdate2`.`Weekth` as `Week_End` FROM `Tbl_Project` as `project` INNER JOIN `Tbl_DimDate` as `dimdate1` 
INNER JOIN `Tbl_DimDate` as `dimdate2`
WHERE `Date_Start` = `dimdate1`.`Tanggal_Id` AND `Date_End` = `dimdate2`.`Tanggal_Id`

edited Mar 02 '17 at 23:11

answered Mar 02 '17 at 23:02

Masoud

333
1
10

@Hogan What is the error? I can run it on MySQL 14.14 – Masoud Mar 03 '17 at 13:46
right... this is using pre-1995 style joins -- so I miss-read. Ugly, but will work. – Hogan Mar 03 '17 at 18:39
@Masoud, it worked well & so helpful. Thank you very much. – Daleman Mar 07 '17 at 04:00

Join Table - Rows to Column

1 Answers1