Inner join SQL query (Sorry, not sure how to succinctly describe)

Question

I believe this will be an extremely simple question. So simple that I am going to be embarrassed. Regardless, I have been trying to figure this out for a while so I'm at my wits end. Here is the scenario using car manufacturers (ie Toyota) and car models (ie Prius, Sienna).

SELECT * FROM car_manufacturer man
INNER JOIN car_model cm ON cm.manufacturerid = man.manufacturerid

Simple, right?

Now I'd like to return a row for each manufacturer that has some Boolean set to true (Lets say car_manufacturer.is_awesome) AND makes more than one model.

I'm trying to use a sub query with select count(*) but can't figure this one out.

Please help!

You are just doing a semi join? i.e. you only want the manufacturers info not all the models as well? Also how are models differentiated? By name? — Martin Smith, Sep 06 '11 at 20:42

Martin Smith · Answer 1 · 2011-09-06T20:45:00.640

4

SELECT man.*
FROM   car_manufacturer man
WHERE  is_awesome = 1
       AND manufacturerid IN (SELECT manufacturerid
                             FROM   car_model
                             GROUP  BY manufacturerid
                             HAVING COUNT(DISTINCT model_name/*whatever*/) > 1)

edited Sep 06 '11 at 20:45

answered Sep 06 '11 at 20:39

Martin Smith

438,706
87
741
845

+1 - I think `IN` will probably be quicker than an `INNER JOIN` – JNK Sep 06 '11 at 20:46
@JNK - Yep. [Hopefully the OP is not on MySQL](http://stackoverflow.com/questions/3417074/why-would-an-in-condition-be-slower-than-in-sql/3417190#3417190) – Martin Smith Sep 06 '11 at 20:47

score 1 · Answer 2 · answered Sep 06 '11 at 20:39

Try this:

SELECT * 
  FROM car_manufacturer man INNER JOIN 
       (
        SELECT manufacturerid  
          FROM car_model 
         GROUP BY manufacturerid  
        HAVING COUNT(1) > 1
       ) cm 
    ON cm.manufacturerid = man.manufacturerid 
   AND man.is_awesome = 1

score 0 · Answer 3 · answered Sep 06 '11 at 20:52

0

SELECT man .manufacturerid 
From car_manufacturer man INNER JOIN car_model car  ON man.manufacturerid = car.manufacturerid 
AND is_awesome = 1
Group by (car  .manufacturerid )
HAVING COUNT(car  .manufacturerid ) > 2

answered Sep 06 '11 at 20:52

sachin saner

109
1
1
11

Inner join SQL query (Sorry, not sure how to succinctly describe)

3 Answers3