How to check, in the middle of a select, relationship existence of a record of a table A with records of other tables

1

I notice that I am a beginner and may be asked something simple, but come on.
I have two tables, PROMOTIONAL and PRODUCTS, where a PRODUCT can relate to zero or many PROMOTIONAL records. What I need is, when I make a select in product, I also bring the information of whether or not there is any related promotional.

Grato.

    
asked by anonymous 29.09.2017 / 14:55

1 answer

0

What you want can be done with the EXISTS clause, which means EXISTENCE :

SELECT prod.*
  FROM produtos prod
 WHERE EXISTS(SELECT 1
                FROM promocional promo
               WHERE promo.prod_id = prod.prod_id)
  

EXISTS

     

When a subquery is presented with the EXISTS keyword, the subquery functions as an existence test. The WHERE clause of the outer query tests whether the rows returned by the subquery exist. The subquery does not actually produce any data; it returns a value of TRUE or FALSE.

    
29.09.2017 / 14:57