Get customer name from table in PostgreSQL-CodePudding

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

CodePudding user response：

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