Select from table1 where table1.id is in table2

Databases
1

Hi there,

I know there are a few ways to do this, but I’m having a brain fart and I’m unsure what the simplest SQL would be.

Basically, I have a table of product brands and a table of products. There can be many products to each brand. On my website I only want to display links to brands that actually have products. So I want a simple query that will select the name of each brand whose ID is referenced in the products table.

Many thanks :slight_smile:

2 
SELECT DISTINCT 
       b.name AS brand_name
  FROM brands AS b
INNER
  JOIN products AS p
    ON p.brand = b.id

simple, innit :wink:

3

Perfect. Exactly what I thought, but for some reason I was convinced that was a better way than using DISTINCT. Anyway, does the job nicely, thanks :slight_smile:

4

here’s another method

SELECT name AS brand_name
  FROM brands
 WHERE EXISTS
       ( SELECT NULL
           FROM products
          WHERE brand = brands.id )

you might want to test both (and/or do an EXPLAIN on both) to see which one runs better in your environment