SQL returning the same result multiple times

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 

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

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

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

You are missing the join between the two tables.

Add something like

 AND  a.user_id = u.user_id