How to DISTINCT in INNER JOIN in mysql?

Question

I want to distinct root_master.rootName but I don't know how to perform this action. Any suggestion how can I get distinct result of root_master.rootName

SELECT DISTINCT
    dispatch_master.dispatch_id,
    dispatch_master.root_id,
    dispatch_master.dispatch_quantity,
    dispatch_master.returned_quantity,
    dispatch_master.disposed_quantity,
    dispatch_master.date,
    root_master.rootName
FROM dispatch_master
INNER JOIN root_master
    ON dispatch_master.root_id = root_master.r_id
    AND dispatch_master.root_id != '1'
    AND dispatch_master.date = CURRENT_DATE()

OUTPUT: output for this innerjoin

Answer 1

Try this:

SELECT DISTINCT
       ##dispatch_master.dispatch_id  <<< remove this
      dispatch_master.root_id
    , dispatch_master.dispatch_quantity
    , dispatch_master.returned_quantity
    , dispatch_master.disposed_quantity
    , dispatch_master.date
    , root_master.rootName
FROM dispatch_master
INNER JOIN root_master
    ON dispatch_master.root_id = root_master.r_id
    AND dispatch_master.root_id != '1'
    AND dispatch_master.date = CURRENT_DATE()

For every column you include you INCREASE the probability of MORE distinct rows. Conversely, reduce the columns can also reduce the number of distinct rows.