MySQL SELECT in SELECT-clause or in WHERE-clause in order to filter further-CodePudding

I'm trying to filter some data, but as the data comes from 2 tables in the database I have to union them together, permitting nulls from the second table as I always need to have any values available in the first table, due to this being a product database with some products having sub-combinations and some none. Thus far I've come up with using a Union to join the two tables together, but now I need a method to filter out the data using a WHERE clause; however: this is where I get stuck. I tried putting the union as a select statement in the FROM clause: no data returned, I tried to put it into the SELECT clause as a sub: no data returned...

In short I need something like this:

SELECT id_product, id_product_attribute,upc1,upc2 
FROM (UNION) 
WHERE upc1='xyz' OR upc2='xyz';

where for example the result might be things such as:

-> 100, null, 9912456, null

-> 200, 153, 9915559, 9977123

Currently I have this (sorry I don't have more):

(SELECT     product.id_product as id_product, 
            product.upc as upc1, 
            comb.id_product_attribute, 
            comb.upc as upc2
 FROM   `db`.table1 product
   LEFT JOIN `db`.table2 comb
   ON comb.id_product = product.id_product
)
UNION
(SELECT     product.id_product as id_product, 
            product.upc as headCNK, 
            comb.id_product_attribute, 
            comb.upc
 FROM   `db`.table1 product
   RIGHT JOIN `db`.table2 comb
   ON comb.id_product = product.id_product
);

Also note that upc1 is coming from table 1, and upc2 from table2.

I could use the entire query, and filter out everything using some business logic in the worst case scenario, but rather not as I don't want to perform endless queries where I don't have to, my service provider doesn't like that...

UPDATE: I also tried:

SELECT * 
from db.t1 as prod 
CROSS JOIN db.t2 as comb ON prod.id_product = comb.id_product 
WHERE prod.upc = 'xyz' OR comb.upc = 'xyz';

This didn't work either.

Placed a fiddle here with some small sample data: http://sqlfiddle.com/#!9/340d7d

The output for the '991002' used in the where clause in query SELECT id_product, id_product_attribute, table1.upc, table2.upc should be: 101, null, 991002, null

And for '990001' it should then be: 101, 201, 990001, 990001

CodePudding user response：

For all values try

SELECT t1.id_product, t2.id_product_attribute, t1.upc, t2.upc
FROM ( SELECT upc FROM table1
       UNION 
       SELECT upc FROM table2 ) t0
LEFT JOIN table1 t1 USING (upc)
LEFT JOIN table2 t2 USING (upc)

For definite upc value edit to

...
SELECT t1.id_product, t2.id_product_attribute, t1.upc, t2.upc
FROM ( SELECT 990001 upc ) t0
LEFT JOIN table1 t1 USING (upc)
LEFT JOIN table2 t2 USING (upc)
...