Having trouble with Inner Join SQL

Question

I've got two tables:

Cyclist = Dob, name, ISO_id

Country = country_name, gdp, population, ISO_id.

I need to query to get the Cyclist.name, Country.country_name, Country.gdp, Country.population.

Using two dates which are entered by the user. These two dates are used to search all cyclists who were born between the dates.

This is what i've tried so far:

SELECT Cyclist.name, Country.country_name, Country.gdp, Country.population
FROM Cyclist
INNER JOIN Country
ON Cyclist.ISO_id = Country.ISO_id
WHERE Cyclist.dob between '$date1' AND '$date2'

however it's not working and I dont know why.

Can anyone offer any insight?

Answer 1

You can use

SELECT Cyclist.name, Country.country_name, Country.gdp, Country.population
FROM Cyclist
LEFT JOIN Country
ON Cyclist.ISO_id = Country.ISO_id
Cyclist.dob>='$date1' and Cyclist.dob<='$date2'

Answer 2

You have enclosed whole Cyclist.dob in WHERE clause, You need seperate enclose both Cyclist and dob to work like.

SELECT Cyclist.name, Country.country_name, Country.gdp, Country.population
FROM Cyclist
INNER JOIN Country
ON Cyclist.ISO_id = Country.ISO_id
WHERE `Cyclist`.`dob` between '$date1' AND '$date2'

Answer 3

Use left join for select all right recods from Cyclist and extend response by data from Country:

SELECT Cyclist.name, Country.country_name, Country.gdp, Country.population
FROM Cyclist
LEFT JOIN Country
ON Cyclist.ISO_id = Country.ISO_id
WHERE `Cyclist.dob` between '$date1' AND '$date2'