INNER JOIN not generating expected results

[bravo14]

Hi guys

I am trying to put a query together to find results from two tables where the field date_id are the same

$next_fixt_sql=('SELECT * FROM `tbl_fixture_dates` INNER JOIN `tbl_fixtures` ON `tbl_fixture_dates`.`date_id` AND `tbl_fixtures`.`date_id` WHERE `tbl_fixture_dates`.`match_date` > NOW( ) AND `tbl_fixtures`.`team_id` =1 LIMIT 1');

The result shows the correct date but the wrong opponent.

Any ideas

[sneakyimp]

An inner join on date alone will not take into account that certain opponents play each other. Your ON clause or WHERE in your query should take that into account.

[bravo14]

It is only for one team's opponents, (my hockey club), and displaying our next fixture based on the date, is there any error in the query?

[sneakyimp]

SELECT * FROM `tbl_fixture_dates`
INNER JOIN `tbl_fixtures`
ON `tbl_fixture_dates`.`date_id` = `tbl_fixtures`.`date_id`
WHERE `tbl_fixture_dates`.`match_date` > NOW( ) AND `tbl_fixtures`.`team_id` =1 LIMIT 1

I changed the AND to an = in your ON clause. I'm not certain but I believe your previous query merely required these date_id fields to have any value at all rather than being equal between the two tables.

Also, I don't know what your db tables might look like so I have no idea how to improve on it. I feel I should point out that you are only selecting values from tbl_fixture_dates and no values at all from tbl_fixtures.

[bravo14]

All resolved, thanks again for your help