SQL for 2 tables

I have these 2 tables:

Table A

Country    State
-----------------
 US         OH
 US         FL
 US         WA


Table B

State    LastVisitDate   City
----------------------------------
 OH        15/10/2019    Bedford
 FL        10/12/2019    Bell
 WA        20/09/2019    Perth

Table A can be linked with table B by field “State”.

What would be the query to get the latest (last visit date) in country=US, doesn’t matter which state, e.g.:

US,10-12-2019,FL,Bell

I tried inner join but couldn’t make it work.

Answer

Always try to explain your question with sample data and query you tried for.

Here is query with some sample data:

CREATE TABLE A
(
    COUNTRY VARCHAR(10),
    State VARCHAR(10)
);

INSERT INTO A VALUES('US','OH'),
('US','FL'),
('AU','WA');

CREATE TABLE B
(
    State VARCHAR(10),
    LastVisitDate DATE,
    City VARCHAR(20)
);

INSERT INTO B VALUES
('OH','2019-10-15','Bedford'),
('FL','2019-12-10','Bell'),
('WA','2019-09-20','Perth');

Expected Result:

US,10-12-2019,FL,Bell

Query:

SELECT a.COUNTRY,b.LastVisitDate,b.State,b.City
FROM A
INNER JOIN B ON a.State = b.State 
WHERE a.COUNTRY = 'US'
ORDER BY b.LastVisitDate DESC
Limit 1;

SQL Fiddle