CODEWITHSUNDEEP
phpmysqlsql
Amr SubZero
Amr SubZero

Reputation: 1276

MySQL search in two tables and return results from the first table

I have two tables Products and Items.

each product contains items relationship by product_id

Products table :

| id    | name          |
|----   |-----------    |
| 1     | Product 1     |
| 2     | Product 2     |

Items table :

| id    | product_id    | name      |
|----   |------------   |--------   |
| 1     | 1             | Item 1    |
| 2     | 2             | Item 2    |

So i have searching ability for table Products :

SELECT * FROM products WHERE name LIKE '%product 1%'

What i'm trying to achieve is the ability to search also on Items table if the there's an item matching return it's parent Product.

So if i search for Item 1 and it's linked to Product 1 by product_id then return Product 1

I've tried :

(SELECT * FROM products WHERE name LIKE '%product 1%') UNION (SELECT * FROM items WHERE name LIKE '%item 1%')

When the search keywords matches Product 1 it returns the product, but if search keywords is Item 1 it returns the item not the product, and i want in all cases to return only products.

How can i achieve this?

Thanks

Upvotes: 0

Views: 348

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I would use:

select p.*
from products p
where p.name LIKE '%search term%' or
      exists (select 1
              from items i
              where i.product_id = p.id and
                    i.name LIKE '%search term%'
             );

With this formulation, you don't have to worry about duplicates if multiple items match a product.

Upvotes: 1

Nick
Nick

Reputation: 147146

You can LEFT JOIN the two tables to each other on product_id and then search in both name fields to find a match, returning the product name if you do. We use a LEFT JOIN so that products that don't have items can still be searched. For example (searching for a product name):

SELECT p.id, p.name
FROM products p
LEFT JOIN items i ON i.product_id = p.id
WHERE p.name LIKE '%Product 1%'
   OR i.name LIKE '%Product 1%'

Output:

id  name
1   Product 1

Or searching for an item name:

SELECT p.id, p.name
FROM products p
LEFT JOIN items i ON i.product_id = p.id
WHERE p.name LIKE '%Item 1%'
   OR i.name LIKE '%Item 1%'

Output:

id  name
1   Product 1

Demo on dbfiddle

Upvotes: 2

Barmar
Barmar

Reputation: 780798

You need to UNION with a JOIN.

SELECT *
FROM products
WHERE name LIKE '%search term%'

UNION

SELECT p.*
FROM products AS p
JOIN items AS i ON p.id = i.product_id
WHERE i.name LIKE '%search term%'

Upvotes: 2

Related Questions