How to verify, amid a select, the existence of relationship of a record of an A table with records of other tables

Question

I already know I’m a beginner and maybe I’m asking something simple, but come on.
I have two tables, PROMOTIONAL and PRODUCTS, and a PRODUCT can relate to zero or many PROMOTIONAL records. What I need is, when making a select in product, also bring information of whether or not there is any promotional related.

Grateful.

Answer 1

What you want can be accomplished with the clause EXISTS, 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 subconsultation is presented with the EXISTS keyword, the subconsulta functions as a test of existence. The WHERE clause of the external query tests whether the lines returned by the subconsulta exist. The subconsultation does not actually produce any data; it returns a TRUE or FALSE value.