SELECT partner_id FROM parking_test WHERE test_date=CURRENT_DATE
Using this query, we get partner details who have a test today.
The customer name is present in another table res_partner
:
SELECT name FROM res_partner rs
I have tried following code:
SELECT name, partner_id FROM (SELECT rs.name FROM res_partner rs) rs LEFT JOIN (SELECT partner_id FROM parking_test) pl ON rs.id = pl.ipartner_id WHERE test_date = CURRENT_DATE
But I got an error
ERROR: column rs.id does not exist LINE 1: …t join (select partner_id from parking_test)pl on rs.id=pl.i…
res_partner
table:
+------------+-------+ | id | name | +------------+-------+ | 34567 | XYZ1 | | 34568 | XYZ2 | | 34569 | DDHC | | 34566 | DVDV | | 34570 | DVFDV | | 34576 | FVFV | | 34567 | FVV | +------------+-------+
parking_test
table
+------------+-----------+ | Partner_id | test_date | +------------+-----------+ | 34567 | 11/06/2021| | 34568 | 11/06/2021| | 34569 | 12/06/2021| | 34566 | 13/06/2021| | 34570 | 14/06/2021| | 34576 | 15/06/2021| | 34567 | 16/06/2021| +------------+-----------+
As we are checking current date I want to get answer as XYZ1 ,XYZ2
Advertisement
Answer
Use INNER JOIN and add condition in WHERE claue
— PostgreSQL
SELECT rs.name, pt.partner_id FROM res_partner rs INNER JOIN parking_test pt ON rs.id = pt.partner_id WHERE pt.test_date= CURRENT_DATE