Problem with SQL statement in MySQL

Question

I run the SQL statement:

$sql = "SELECT pic, userid FROM user_details INNER JOIN wp_users ON user_details.userid WHERE wp_users.id = user_details.userid LIMIT $recordstart, $pagesize "; 

The request is intended to select entries from user_details and wp_users, but should only select if there is a match ( wp_users.id = user_details.userid ).

The goal is that it basically selects from the table when retrieving a record (based on identifier matching) from another table

The problem is that it shows duplicate entries. How can I fix this problem?

Answer 1

After inclusion, you must enter the JOIN clause. Like this:

 $sql="select pic, userid from user_details inner join wp_users on (wp_users.id=user_details.userid) limit $recordstart, $pagesize"; 

But the real problem is that you have more than 1 entry in user_details for each corresponding entry in wp_users (or vice versa). INNER JOIN forces the database to do the Cartesian product of user_details and wp_users . Then the result table is filtered by the current WHERE clause (wp_users.id = user_details.userid). Depending on your needs, you can use GROUP BY or DISTINCT if you want to get only unique records. For example, if you need userid be unique:

 $sql="select pic, userid from user_details inner join wp_users on (wp_users.id=user_details.userid) group by userid limit $recordstart, $pagesize"; 

Answer 2

 SELECT DISTINCT pic, userid FROM user_details INNER JOIN wp_users ON wp_users.id=user_details.userid LIMIT $recordstart, $pagesize 

Answer 3

Use DISTINCT to return only unique rows and fulfill the join condition in the ON section:

 $sql="select distinct pic, userid from user_details inner join wp_users on wp_users.id=user_details.userid limit $recordstart, $pagesize"; 

Answer 4

use GROUP BY as it shows duplicate records if there are several records in the second table in one record in the first table

Answer 5

You accidentally lost part of ON .

 $sql="select pic, userid from user_details inner join wp_users on user_details.userid = wp_users.id where wp_users.id=user_details.userid limit $recordstart, $pagesize"; 

Answer 6

I could try

  $sql = "select pic, userid from user_details inner join wp_users on user_details.userid = wp_users.id limit $recordstart, $pagesize";