CODEWITHSUNDEEP
phpmysql
PeteSE4
PeteSE4

Reputation: 327

Finding records in a union - MySQL

I'm probably approaching this the wrong way, but here's the background. I have a table of products and a table of relations between the products - so, for instance, the relationship table might look like this:

ID    PRODUCT 1   PRODUCT 2
1     1           2
2     1           3
3     3           4

So, in the above example, product id 1 is related to products 2 & 3, product id 2 is related to 1 and product id 3 is related to 1 & 4 (since the relationship can be in either column 1 or 2)

So, I'm on the product page and want to find the details of products that are related to the current product. My instinct tells me to do a query like this:

SELECT p.* FROM products p 
WHERE p.id IN (
    (SELECT product_1 AS related FROM relations WHERE product_2 = :this_product) 
    UNION 
    (SELECT product_2 AS related FROM relations WHERE product_1 = :this_product)
)

But that gives me a syntax error because of the UNION.

So, am I doing this all wrong and should be taking a completely different approach or have I just made a minor syntax boo-boo with my query?

Upvotes: 0

Views: 30

Answers (3)

SagarPPanchal
SagarPPanchal

Reputation: 10121

Below is the example, you can also try this 

SELECT p.*,p1.product_1
FROM products p 
LEFT JOIN relations as rel
ON p.id = relational_field 
WHERE p.id IN ( product_2, product_1 )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

To get the list of products, you can do something like this:

select (case when :this_product = product_1 then product_2 else product1 end)
from relations r
where :this_product in (product_1, product_2)

Then you can join products back in.

Alternatively, use exists. It is more efficient:

select p.*
from products p
where exists (select 1
              from relations r
              where r.product_1 = :this_product and p.id = r.product_2
             ) or
      exists (select 1
              from relations r
              where r.product_2 = :this_product and p.id = r.product_1
             );

Upvotes: 1

SMA
SMA

Reputation: 37023

You need to remove the brackets used around union like below:

SELECT p.* FROM products p 
WHERE p.id IN (
(SELECT product_1 AS related FROM relations WHERE product_2 = :this_product
UNION 
SELECT product_2 AS related FROM relations WHERE product_1 = :this_product)
)

Check the fiddle here

Upvotes: 0

Related Questions