Why Inner-join shows wrong data with where cluase?

Question

Using where clause in inner join but inner join is not selecting what I asked for in query. Here I am to select only 12-jun data whereas innerjoin query selecting previous date data aswell.

QUERY IS:

SELECT
    p_sale_inv.datetime,
    p_sale_inv.sale_inv,
    p_sale_inv.Jobber,
    p_sale_part.stock_id,
    p_sale_part.quantity,
    p_sale_part.netamount,
    p_purch_stock.Price,
    p_sale_part.sale_inv
FROM
    p_sale_inv
INNER JOIN p_sale_part ON p_sale_inv.sale_inv = p_sale_part.sale_inv
INNER JOIN p_purch_stock ON p_sale_part.stock_id = p_purch_stock.stock_id
WHERE
    DATE_FORMAT(p_sale_inv.datetime, '%Y-%m-%d') BETWEEN '2018-07-12' AND '2018-07-12'

PROBLEM IS: Why query showing same date? In actual as you can see above table they are different.

Answer 1

The query is returning the second row of the p_sale_inv table, joined to two rows of the p_sale_part table. That is why the dates are the same: they are from the same row of the table.

(Note that only one p_sale_inv row has a date that matches the WHERE clause. So you wouldn't expect both rows of that table to be returned.)

Edit: Maybe this will help clear up the misunderstanding. Here is the same query with the INNER JOIN's removed:

SELECT
    p_sale_inv.datetime,
    p_sale_inv.sale_inv,
    p_sale_inv.Jobber,
FROM
    p_sale_inv
WHERE
    DATE_FORMAT(p_sale_inv.datetime, '%Y-%m-%d') BETWEEN '2018-07-12' AND '2018-07-12'

You will see only one row from the table, the one with a 7-12 date. If you remove the 'WHERE' clause, you will see both rows, and both dates. It is the join's to other tables that are confusing you. I think your next step is to learn about how table joins work.