MySQL Forums
Forum List  »  General

Compound Join Condition Within Same Table - How do you do this?
Posted by: Jeremy Pyle
Date: April 28, 2020 08:47PM

I have a database set up called WordpressDB. I want to search within the same table. The table is called: rent

I have a column called recon_date. Another column is created off this column by subtracting 1 day.

For any row, I want to search for the other occurence that is 1 day earlier

For example for id 228, the palace_tenant_code is RBTG104 and the recon_date is 29/04/2020. The early_date is 28/04/2020. I want to search for the corresponding row that has 28/04/2020 in the recon_date column and RBTG104 in the palace_tenant_code column

This is my table so far

id palace_tenant_code recon_date early_date
202 RBTG101 28/04/2020 27/04/2020
203 RBTG102 28/04/2020 27/04/2020
204 RBTG103 28/04/2020 27/04/2020
205 RBTG104 28/04/2020 27/04/2020
206 RBTG105 28/04/2020 27/04/2020
225 RBTG101 29/04/2020 28/04/2020
226 RBTG102 29/04/2020 28/04/2020
227 RBTG103 29/04/2020 28/04/2020
228 RBTG104 29/04/2020 28/04/2020
229 RBTG105 29/04/2020 28/04/2020
230 RBTG106 29/04/2020 28/04/2020
231 RBTG107 29/04/2020 28/04/2020
232 RBTG108 29/04/2020 28/04/2020
233 RBTG109 29/04/2020 28/04/2020
234 RBTG110 29/04/2020 28/04/2020


This is my query code. Do you have any ideas why this is not working?
USE WordpressDB;
SELECT id, palace_tenant_code, recon_date, recon_date - INTERVAL 1 DAY AS early_date
FROM rent rt
JOIN rent ry
ON rt.early_date = ry.recon_date
AND rt.palace_tenant_code = ry.palace_tenant_code


This code doesn't seem to work. I have also tried:
USE WordpressDB;
SELECT rt.id, rt.palace_tenant_code, rt.recon_date, rt.recon_date - INTERVAL 1 DAY AS early_date
FROM rent rt
JOIN rent ry
ON rt.early_date = ry.recon_date
AND rt.palace_tenant_code = ry.palace_tenant_code

Anyone have any ideas why this isn't working? Basically I want to join row 204 to row 228

Options: ReplyQuote


Subject
Written By
Posted
Compound Join Condition Within Same Table - How do you do this?
April 28, 2020 08:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.