Retrieve from only one table

benton · 11-04-2008, 03:54 AM

I'm trying to alter a search function that searches a number of tables for some word. The problem I am having is that the word can exist in more than one table so it is returning duplicate results.

For example, in the pseudo code below, if the name being searched for (sally) appears in tables B or C, then the address from the initial table should be returned. This part works. But if the name is in B and C, then two results are returned. So I need to be able to check the tables B and C with an exclusive or condition.

tableA {
id = 1;
name = sally;
address = 123 Street
}

tableB {
id = 1;
name = sally;
}

tableC {
id = 1;
name = sally;
}

select a.address from
tableA a left join
tableB b on a.id = b.id left join
tableC c on a.id = c.id
where b.name like %sally% or (b.name not like %sally% and c.name like %sally%)

I thought the above would filter out the second occurrence but it still shows up. Can anyone see a way to solve this, please?

Jim · 11-04-2008, 06:40 AM

Use the UNION function in MySQL :)

MySQL UNION Keyword

benton · 11-06-2008, 01:38 AM

Thanks for the suggestion. I made the change to the actual code but it fails. Here is what the display looks like in phpmyadmin when it fails. Can you (anyone) see the problem?

Code:

SELECT count( DISTINCT p.products_id ) AS total
FROM products p, products_description pd, categories c, products_to_categories p2c
UNION (
SELECT pov.products_options_values_name
FROM products_attributes pa ON p.products_id = pa.products_id
LEFT JOIN products_options_values pov ON pa.options_values_id = pov.products_options_values_id
)
WHERE p.products_id = pd.products_id
AND p.products_id = p2c.products_id
AND p2c.categories_id = c.categories_id
AND (
(
pd.products_name LIKE '%premium%'
OR p.products_model LIKE '%premium%'
OR products_options_values_name LIKE '%premium%'
)
)

The failure is:

Quote:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on p.products_id = pa.products_id left join products_options_val