CODEWITHSUNDEEP
mysql
ujjwalwahi
ujjwalwahi

Reputation: 342

Unique result set with inner join query

SELECT 
    * 
FROM `catalog_webdesign_products` t 
INNER JOIN tbl_member_registration t1 ont.userid=t1.fld_loginid 
WHERE 1 
    AND t1.fld_member_category_level<9 
    AND t.product_img IS NOT NULL  
ORDER BY RAND() LIMIT 5

In this query , I need rows with unique userid. I had tried 'group by userid' also but then product_img with null value is coming in result set.

Upvotes: 1

Views: 220

Answers (2)

echo_Me
echo_Me

Reputation: 37233

yes you can use GROUP BY userid. but your query looks wrong here

   INNER JOIN tbl_member_registration t1 on t.userid=t1.fld_loginid
                                           ^------------------------space here

try this

  SELECT 
  * 
  FROM `catalog_webdesign_products` t 
  INNER JOIN tbl_member_registration t1 on t.userid=t1.fld_loginid 
  WHERE  t1.fld_member_category_level<9 
  AND t.product_img IS NOT NULL  
  group by userid
  ORDER BY RAND() LIMIT 5

edit:

 SELECT 
  * 
  FROM (select * from `catalog_webdesign_products` where product_img IS NOT NULL) t 
  INNER JOIN tbl_member_registration t1 on t.userid=t1.fld_loginid 
  WHERE  t1.fld_member_category_level<9 
  group by t.userid
  ORDER BY RAND() LIMIT 5

Upvotes: 2

Zeal
Zeal

Reputation: 249

For better performance you should move the condition for t1 into join statement. You problem could be the wrong JOIN condition. Try LEFT JOIN instead of INNER JOIN.

More about joins: http://www.w3schools.com/sql/sql_join.asp

SELECT * 
FROM `catalog_webdesign_products` t 
LEFT JOIN tbl_member_registration t1 
ON (t.userid = t1.fld_loginid AND t1.fld_member_category_level < 9)
WHERE t.product_img IS NOT NULL 
GROUP BY t.userid 
ORDER BY RAND() LIMIT 5

Upvotes: 0

Related Questions