Suneth Kalhara Suneth Kalhara - 3 months ago 6
MySQL Question

MySQL normal query vs Join query different results

I'm working on a search function which use for product search.

But when i try to use MySQL join it gives different results.

tbl_product table have a product which have sku number pd_art 42504.

so it should give a one results when i search using 42504

when i use normal query it gives 1 results

SELECT *
FROM tbl_product, tbl_suggetions
WHERE
(
(tbl_product.pd_name LIKE '%42504%') OR
(tbl_product.pd_art LIKE '%42504%') OR
(tbl_product.pd_srkw LIKE '%42504%') OR
(tbl_suggetions.sgtexts LIKE '%42504%' AND
tbl_product.pd_id = tbl_suggetions.sgproduct)
) AND pd_bestsell <> 1
GROUP BY pd_id
ORDER BY RAND()


But when use a join it doesn't give that product as results

SELECT *
FROM tbl_product tbl_product
JOIN tbl_suggetions tbl_suggetions
ON tbl_suggetions.sgproduct = tbl_product.pd_id
WHERE
(
(tbl_product.pd_name LIKE '%42504%') OR
(tbl_product.pd_art LIKE '%42504%') OR
(tbl_product.pd_srkw LIKE '%42504%') OR
(tbl_suggetions.sgtexts LIKE '%42504%')
) AND pd_bestsell <> 1
GROUP BY pd_id
ORDER BY RAND()


Can anyone help me to fix this please, i really need to use join query because its 20 time faster than normal query execution. Thanks a lot

Answer

In your first query, the a record would be retained in the result set even if the join did not match up under the following conditions:

tbl_product.pd_name LIKE '%42504%' OR
tbl_product.pd_art LIKE '%42504%' OR
tbl_product.pd_srkw LIKE '%42504%'

I believe that when you switch to INNER JOIN (your second query), the record which has sku number pd_art 42504 is being filtered off because it has no suggestions. One option to retain this record (as @James mentioned moments before I posted), would be to LEFT JOIN the two tables together:

SELECT *
FROM tbl_product tbl_product 
LEFT JOIN tbl_suggetions tbl_suggetions 
    ON tbl_suggetions.sgproduct = tbl_product.pd_id 
WHERE
(
    tbl_product.pd_name LIKE '%42504%' OR
    tbl_product.pd_art LIKE '%42504%' OR
    tbl_product.pd_srkw LIKE '%42504%' OR
    tbl_suggetions.sgtexts LIKE '%42504%'
) AND pd_bestsell <> 1 
GROUP BY pd_id 
ORDER BY RAND()

If this hypothesis be correct, then for your matching records all the columns from the tbl_suggetions table should be NULL.