Problem with SQL statement in MySQL

Question

I am running an 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 query is meant to select records from user_details and wp_users, but should only select if there is a match (wp_users.id = user_details.userid).

The target is for it to basically select from a table while retrieving a record (based on the id match) from the other table

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

First, do you know that your query is wrong? That cant' be the one you're running. Also, what do you want to do when there are multiple user_details rows for a user? Just pick one? Or will you not be getting anything from the details at all, just ensuring there are details? Or do you need all the data from many details in a single row (PIVOT)? — ErikE
– ErikE, Commented Jun 10, 2011 at 23:11

Ivan Nevostruev · Accepted Answer · 2011-06-10 20:16:31Z

3

You should put your JOIN condition after ON. 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 then 1 record in user_details for each corresponding record in wp_users (or vise versa). INNER JOIN is causing database to make a Cartesian product of user_details and wp_users records. Then result table is filtered by your current WHERE condition (wp_users.id=user_details.userid). Depends on your needs you can use GROUP BY or DISTINCT if you want to retrieve unique records only. For example, if you need userid to 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";

edited Jun 10, 2011 at 20:16

answered Jun 10, 2011 at 19:56

Ivan Nevostruev

28.9k8 gold badges69 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ErikE Over a year ago

Doesn't MySQL support some kind of semi-join (EXISTS)? Using that seems more correct than a GROUP BY.

Ivan Nevostruev Over a year ago

@Erik Yes, there are EXISTS in mysql. But it really depends on what OP is trying to achieve with the query. The question is: what to return if there are 2 user_details for single wp_users?

ErikE Over a year ago

Given that he's not selecting any columns from that table and said "only if there is a match", that sure sounds like a semi-join to me! Well, wait... he did say "retrieve a record" so maybe he does need something from the second table. You're right.

Andrew Lazarus Over a year ago

MySQL is the only major DB that will accept having the pic column not aggregated in a GROUP BY query. I find that can lead to hard-to-find bugs. Might be better to say MAX(pic) or MIN(pic) so that the choice per userid is not arbitrary.

Steve Mayne · Accepted Answer · 2011-06-11 11:25:12Z

2

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

edited Jun 11, 2011 at 11:25

answered Jun 10, 2011 at 19:55

Steve Mayne

23k4 gold badges53 silver badges49 bronze badges

4 Comments

ErikE Over a year ago

The question says he's getting duplicate rows. Maybe you could address that in your answer.

Steve Mayne Over a year ago

I have now addressed it with DISTINCT.

ErikE Over a year ago

I didn't answer because of the plethora of answers and the questioner's not specifying how he wants to handle multiple child rows. Plus I am not an expert in MySQL. But it would be nice to address the poster's whole question instead of just posting a syntax correction of his query.

Steve Mayne Over a year ago

Because of the lack of join criteria I was under the impression he was ending up with a cartesian product of his result set (across all rows in the user_details table). So fixing the SQL was indeed the solution. I might be wrong, but I was making an attempt to answer the question.

Devin Burke · Accepted Answer · 2011-06-10 20:03:38Z

1

Use DISTINCT to return only unique rows and complete your 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";

edited Jun 10, 2011 at 20:03

answered Jun 10, 2011 at 19:56

Devin Burke

13.8k12 gold badges60 silver badges85 bronze badges

1 Comment

ErikE Over a year ago

Doesn't MySQL support some kind of semi-join (EXISTS)? Using that seems more correct than a GROUP BY or a DISTINCT.

SergeS · Accepted Answer · 2011-06-10 19:54:44Z

0

use GROUP BY , because it shows duplicate records if there is multiple records in second table per on one record in first table

answered Jun 10, 2011 at 19:54

SergeS

11.9k3 gold badges31 silver badges35 bronze badges

3 Comments

ErikE Over a year ago

Doesn't MySQL support some kind of semi-join (EXISTS)? Using that seems more correct than a GROUP BY.

SergeS Over a year ago

in fact no ( only EXISTS or IN ... , but it isn't join ), because in thery JOIN combines record ( eg. if you have m:n constraint , it will return any availible combination )

ErikE Over a year ago

Using EXISTS with an outer reference IS a join. It's called a semi-join or an anti-semi-join.

Andrew Lazarus · Accepted Answer · 2011-06-10 19:57:09Z

0

You left out the = part of the ON by accident.

$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";

answered Jun 10, 2011 at 19:57

Andrew Lazarus

19.5k3 gold badges38 silver badges57 bronze badges

1 Comment

ErikE Over a year ago

The question says he's getting duplicate rows, which you didn't answer. And did you notice the WHERE clause is identical to the ON?

sam · Accepted Answer · 2011-06-10 20:00:09Z

0

I might try

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

answered Jun 10, 2011 at 20:00

sam

8131 gold badge6 silver badges7 bronze badges

1 Comment

ErikE Over a year ago

The question says he's getting duplicate rows. Maybe you could address that in your answer.

Collectives™ on Stack Overflow

Problem with SQL statement in MySQL

6 Answers 6

4 Comments

4 Comments

1 Comment

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

4 Comments

1 Comment

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related