CODEWITHSUNDEEP
phpmysqlmysqlirows
A.Codera
A.Codera

Reputation: 29

My MySQL SELECT Query is Returning Multiples of the Same Rows

For some reason I'm getting just one duplicate row that I can see, but can't really pinpoint where in the query I've gone wrong.

$stmt = $mysqli->query("
    SELECT DISTINCT vendors_tbl.email AS email,
    (vendor_avails_tbl.standard_pricing - vendor_loc_tbl.offpeak_time_pricing) AS best_margins,
    vendor_loc_tbl.location_id AS locationID,
    vendor_loc_tbl.loc_img_path AS locImg, 
    vendor_loc_tbl.offpeak_time_pricing AS offpeak,
    vendor_loc_tbl.address1 AS address1,
    vendor_loc_tbl.address2 AS address2,
    vendor_loc_tbl.zip_code AS zip,
    vendor_loc_tbl.geocodes AS geo,
    vendor_loc_tbl.has_valet AS valet,
    vendor_loc_tbl.has_transport AS transport,
    vendor_loc_tbl.has_wheelchair AS wheelchair,
    vendor_loc_tbl.has_desk AS desk,
    vendor_loc_tbl.has_24hours AS open24hrs,
    vendor_loc_tbl.has_covered AS covered,
    vendor_loc_tbl.has_security AS security, 
    (vendor_avails_tbl.available_economy + vendor_avails_tbl.available_standard + vendor_avails_tbl.available_midsize + vendor_avails_tbl.available_truck_suv) AS avail_total, 
    vendor_avails_tbl.standard_pricing AS standard_pricing, 69 *
    DEGREES(ACOS(COS(RADIANS($e_lat))
         * COS(RADIANS(SUBSTR(vendor_loc_tbl.geocodes, 1, 10)))
         * COS(RADIANS($e_lon) - RADIANS(SUBSTR(vendor_loc_tbl.geocodes, 13)))
         + SIN(RADIANS($e_lat))
         * SIN(RADIANS(SUBSTR(vendor_loc_tbl.geocodes, 1, 10))))) AS distance_in_m
    FROM vendors_tbl
    INNER JOIN vendor_loc_tbl ON vendor_loc_tbl.vendor_id = vendors_tbl.vendor_id
    INNER JOIN vendor_avails_tbl ON vendor_avails_tbl.location_id = vendor_loc_tbl.location_id
    WHERE vendor_avails_tbl.available_standard > 0
    ORDER BY vendor_loc_tbl.override_level DESC, best_margins DESC, distance_in_m ASC
    LIMIT 5
");

Upvotes: 0

Views: 78

Answers (2)

Dipanwita Kundu
Dipanwita Kundu

Reputation: 1667

Your distinct create the group of all the fields you have specified as you did not specify the group by condition. So there is no chance to get duplicate value.

Upvotes: 1

Filip Koblański
Filip Koblański

Reputation: 9988

Well In some cases it's bettor to use GROUP BY statement than DISTINCT. In your example you should add a group by at least on one col (some unique for example id). When you're using aggregates in query it is better to use group by, when using only joins - distinct is enough.

Upvotes: 1

Related Questions