CODEWITHSUNDEEP
mysqlsqlmysql-python
Thaise
Thaise

Reputation: 1103

SQL - Concatenation without primary key

I have two tables (A and B) which do not share primary keys. I need some kind of concatenation in which for each row in Table A I have to add Table B rows if date_B < date_A.

*date format: yyyy-mm-dd

Table A

+-------+-----+-----------+
| id_A  | op_A|   date_A  |
+-------+-----+-----------+
| 1     |  0  | 2018-10-01|
| 2     |  0  | 2018-12-31| 
+-------+-----+-----------+

Table B

+-----+--------+-------------+
| id_b| other  |  date_B     |
+-----+--------+-------------+
| a   |  8     | 2018-09-01  |
| b   |  90    | 2018-12-01  | 
+-----+--------+-------------+

Correct Result:

+-----+-----+-----------+-----+--------+-------------+
| id_A| op_A|   date_A  | id_B| other  |  date_B     |
+-----+-----+-----------+-----+--------+-------------+
| 1   |  0  | 2018-10-01| a   |  8     | 2018-09-01  |
| 2   |  0  | 2018-12-31| a   |  8     | 2018-09-01  |
| 2   |  0  | 2018-12-31| b   |  90    | 2018-12-01  |  
+-----+-----+-----------+-----+--------+-------------+

Upvotes: 1

Views: 42

Answers (1)

Nick
Nick

Reputation: 147206

This query should give you the results you want:

SELECT *
FROM TableA a
JOIN TableB b ON b.date_B < a.date_A

Output:

id_A    op_A    date_A      id_b    other   date_B
1       0       2018-10-01  a       8       2018-09-01
2       0       2018-12-31  a       8       2018-09-01
2       0       2018-12-31  b       90      2018-12-01

Demo on SQLFiddle

Upvotes: 3

Related Questions