JOIN info from one table onto a UNION of two others... Is it possible?

What I'm looking to do is something like this:

SELECT *  
FROM products as p, cat_index1 as c1, cat_index_2 as c2  
WHERE p.pid = c1.cid OR p.pid = c2开发者_Go百科.cid

not that I expected it to work, but EXPLAIN yields the following error:

Impossible WHERE noticed after reading const table...

I've got a list of products and a table for each category, I would like to pull info from more than one category at a time and join it with the product info. Products in the table products can be in both categories, one category or neither. The output needs to be a union of cat_index1 with cat_index2 and I want to join the info from products with said union.

The product table is pretty big, lots of info in there, but the category tables are a single column just containing the product ids of members of said category which are, of course, equal to the id column in the product table. No foreign keys are currently set.

Anyone have any ideas?

---

SELECT *  
FROM products as p
inner join cat_index1 as c1 on (p.pid = c1.cid)

union

SELECT *  
FROM products as p
inner join cat_index2 as c2 on (p.pid = c2.cid)

---

Based on the results you described you desire, I think you're actually looking for an OUTER JOIN. Try this:

SELECT * FROM products
    LEFT OUTER JOIN cat_index1 as c1 ON c1.cid = p.pid
    LEFT OUTER JOIN cat_index2 as c2 ON c2.cid = p.pid

This query will return all the products and their associated records in cat_index1 and cat_index2. If no matching record exists in cat_index1 or cat_index2, you'll still have the products row included in your result set.

---

Try to use

SELECT *  
FROM products as p
where 
EXISTS(select * from cat_index1 as c1 where p.pid = c1.cid) or  EXISTS(select * cat_index_2 as c2  
WHERE p.pid = c2.cid)

---

Try using WHERE IN () and union cat_index1.cid and cat_index_2.cid:

SELECT * FROM Products AS P WHERE P.pid IN
(
SELECT cat_index1.cid
 UNION 
SELECT cat_index_2.cid
)