SQL returning the same result multiple times

Question

I am fairly new with advanced MySQL commands, I have database tables with multiple relationships. I have an advanced search feature that must match Keywords to a few fields (most being in the Assets table but a couple in the User table). When I execute the following Query for desk it returns the same row multiple times, while it should do it once.

 SELECT 
   a.id, a.asset_id, a.name, a.serial_number, a.category_id, a.status_id, a.user_id, a.location_id 
 FROM 
   assets a, users u 
 WHERE 
  (a.asset_ID LIKE '%desk%' OR a.name LIKE '%desk%' OR (u.first_name LIKE '%desk%' OR u.last_name LIKE '%desk%')) AND 
  a.serial_number LIKE '%%' AND 
  a.category_id='2' 
 LIMIT 25 OFFSET 5450

Is the query matching the search keywords multiple times in a single row? This could get you on the right track — Ryan
– Ryan, Commented Jul 14, 2011 at 14:34

Lukas Eder · Accepted Answer · 2011-07-14 15:12:05Z

10

You have a cartesian product in your query. You should JOIN assets and users, e.g. with

WHERE a.user_id = u.id

or like this

FROM assets a JOIN users u ON a.user_id = u.id

UPDATE: Your comment shows that you actually want to "left outer join" the users table. This means, that all assets are in the result set regardless if there exists a matching user:

FROM assets a LEFT OUTER JOIN users u ON a.user_id = u.id

Read more about joining tables here: http://dev.mysql.com/doc/refman/5.5/en/join.html

edited Jul 14, 2011 at 15:12

answered Jul 14, 2011 at 14:34

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

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

5 Comments

Yuck Over a year ago

Please prefer the latter though. Keeping join criteria and selection criteria separate makes for better readability.

Lukas Eder Over a year ago

@Yuck: Absolutely. But one step at a time for newbies :-)

Travis Pessetto Over a year ago

Thank you, this is the exact thing it was missing.

Lukas Eder Over a year ago

@Travis: Generally, if your records seem to duplicate (also in much more complex queries), you can always search for cartesian products first. 95% you'll find the problem there

Travis Pessetto Over a year ago

There is a new problem I am running into with this, if the Asset is not assigned to a user it is not showing up, even if searched for. I am assuming it is because it has no match for a.user_id = u.id. Is there a way to maybe add OR a.user_id = NULL (I already tried this, but it didn't work)?

Keith · Accepted Answer · 2011-07-14 14:38:48Z

1

You could use a SELECT DISTINCT clause, though the actual problem looks like the one made by Lukas. It's good practice to use explicit joins like so:

 FROM assets a
 JOIN users u ON a.user_id=u.id

answered Jul 14, 2011 at 14:38

Keith

2,6284 gold badges29 silver badges44 bronze badges

1 Comment

Lukas Eder Over a year ago

Hmm, true. DISTINCT would be correct in this case, without projections from users. Your DBA would kill you ;-)

Tom H · Accepted Answer · 2011-07-14 14:40:51Z

You need to actually join the two tables (Assets and Users) together. As you have it, every row in each table is matched with every row in the other. This is known as a Cartesian Product and is usually a bad thing.

I would also suggest that you start using proper JOIN syntax:

SELECT
    a.id,
    a.asset_id,
    a.name,
    a.serial_number,
    a.category_id,
    a.status_id,
    a.user_id,
    a.location_id
FROM
    Assets A
INNER JOIN Users U ON A.user_id = U.user_id
WHERE
    (
        a.asset_ID LIKE '%desk%' OR
        a.name LIKE '%desk%' OR
        (
            u.first_name LIKE '%desk%' OR
            u.last_name LIKE '%desk%'
        )
    ) AND
    a.serial_number LIKE '%%' AND
    a.category_id='2'
LIMIT 25
OFFSET 5450

Jeff Moore · Accepted Answer · 2011-07-14 14:37:12Z

0

You are missing the join between the two tables.

Add something like

 AND  a.user_id = u.user_id

answered Jul 14, 2011 at 14:37

Jeff Moore

4442 silver badges10 bronze badges

Collectives™ on Stack Overflow

SQL returning the same result multiple times

4 Answers 4

5 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related