Selecting multiple values different tables

Question

Hi,

I'm relatively new to MySql.

I have 2 tabled with the following structure

products
{
pid-autoincrement,
pname
p_desc
}

services
{
sid-autoincrement
s_name
s_desc
}

Im trying to select the products or services which have the name '%<somekeyword>%'

I'm using the query:

SELECT DISTINCT products.*, 
                services.* 
           FROM products, services 
          WHERE products.pname 
                LIKE '%mob%' 
             OR services.s_name
                LIKE '%mob%'

But I'm getting a lot of repeated results.

Tried using joins but couldn't get a solution.

Can someone help me with this?

Answer 1

you have to use the UNION operator

therefore you can only select the same columns (as there is only one result)

or you select 2 times - 2 results

Answer 2

You want to use UNION, like this:

SELECT DISTINCT products.pid-autoincrement AS id, 
                products.pname AS name, 
                products.p_desc AS desc 
           FROM products
          WHERE products.pname 
                LIKE '%mob%' 
UNION
SELECT DISTINCT services.sid-autoincrement AS id, 
                services.s_name AS name, 
                services.s_desc AS desc 
           FROM services
          WHERE services.s_name 
                LIKE '%mob%'

Answer 3

It worked!!

Thanks a lot :)

Answer 4

Because the tables aren't related you'll have to use the UNION operator (as karlis says), rather than JOIN. But as you apparently want to handle products and services together (at least some of the time) you're probably better off putting them into one table and adding a column to differentiate between them, like so:

productsandservice [need better name]
{
id,
name,
desc,
isaservice
}