Get customer name from table in PostgreSQL

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

>Solution :

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 

Leave a Reply