Returning all values in another table that matches one linked value in another

Sorry the title is a bit convoluted!

I've had a little search everywhere but I'm not sure I'm wording it right to get any results or if what I want to do is even possible...

Basically I want to return all values in a table that match a value in another. I have the tables:

开发者_如何转开发

Table "Cars"               Table "Models"

Car_ID    Car              Car_ID    Model_ID    Model
-------------------        ----------------------------
1         Ford             1          14         Mustang
2         Fiat             1          21         Focus
3         Toyota           1          87         Escort
                           2          78         500
                           2          45         Punto
                           3          66         Aygo

And if I search for just one model name (eg. Escort) I want these results:

Car    Model_ID    Model
------------------------
Ford   14          Mustang
Ford   21          Focus
Ford   87          Escort

So searching for a car model will return all the models that are the same make.

Any one have any ideas?

---

Is this what you're looking for?

SELECT c.Car, m.Model_ID, m.Model
FROM Models m
INNER JOIN Cars c ON c.Car_ID = M.Car_ID
WHERE m.Car_ID = (SELECT Car_ID FROM Models WHERE Model = 'Escort');

---

try this:

 select c.Car, m.Model_ID, m.Model from Cars c, Models m where c.Car_ID=m.Car_ID 
and Car_ID in (select Car_ID from Models where  Model='Escort')

---

You could use this query. If there are multiple models with the same name, this query will return all of the models from all of the corresponding makes of the provided model name.

SELECT c.Car, m.Model_ID, m.Model

FROM Models AS m_search

INNER JOIN Cars AS c
ON m_search.Car_ID = c.Car_ID

INNER JOIN Models AS m
ON c.Car_ID = m.Car_ID

WHERE m_search.Model = ?

Example on PostgreSQL (since MySQL doesn't support WITH):

chris=$ WITH Cars (Car_ID, Car) AS (VALUES
    (1, 'Ford'),
    (2, 'Fiat'),
    (3, 'Toyota')
), Models (Car_ID, Model_ID, Model) AS (VALUES
    (1, 14, 'Mustang'),
    (1, 21, 'Focus'),
    (1, 87, 'Escort'),
    (2, 78, '500'),
    (2, 45, 'Punto'),
    (3, 66, 'Agyo')
)

SELECT c.Car, m.Model_ID, m.Model

FROM Models AS m_search

INNER JOIN Cars AS c
ON m_search.Car_ID = c.Car_ID

INNER JOIN Models AS m
ON c.Car_ID = m.Car_ID

WHERE m_search.Model = 'Escort';

 car  | model_id |  model
------+----------+---------
 Ford |       14 | Mustang
 Ford |       21 | Focus
 Ford |       87 | Escort
(3 rows)

---

You could use a subquery to retrieve the Car_ID for a model:

select  *
from    Models m
join    Cars c
on      c.Car_ID = m.Car_ID
where   m.Car_ID = 
        (
        select  Car_ID
        from    Models
        where   Model = 'Mustang'
        )

---

Depending on how big the tables you're looking at are it can be more efficient to get your information in a sub-query rather than using "in" or joins.

select ( select car from cars where car_id = m.car_id ), model_id, model
  from models m
 where car_id = ( select car_id from from models where model = 'Escort' )