wp INNER JOIN not fetching correct data in my wp website

Question

i created a simple script for my wp website, i am trying to fetch the correct data from wp database of 2 table here is code,

when im displaying the result of column "user_ref_id" is repeating many times, here is code

global $wpdb;
 $tableuser=$wpdb->prefix.'user_shah';
 $tablecopn=$wpdb->prefix.'user_coupons_shah';
$results_count = $wpdb->get_results("SELECT User_Status, Coupon_Status, User_ref_id FROM $tableuser t1 INNER JOIN $tablecopn t2 ON t1.User_Status=t2.Coupon_Status WHERE t1.User_Status='1'");

$res_countus = count($results_count);

if($res_countus>0)
{

 echo "<p><select name='autoref' id='myAutoref' onchange='myRefauto()'>
 <option>Select Reference</option>";
 foreach($results_count as $results2)
 { echo "<option value='{$results2->User_ref_id}'>{$results2->User_ref_id}</option>";

 }
 echo "</select></p>";
}

Answer 1

You need to add GROUP BY clause to your mysql query.

$results_count = $wpdb->get_results("
SELECT User_Status, Coupon_Status,User_ref_id 
FROM $tableuser t1 INNER JOIN $tablecopn t2 
ON t1.User_Status=t2.Coupon_Status 
WHERE t1.User_Status='1' 
GROUP BY User_ref_id ");