在另一个表中返回与另一个表中的一个链接值匹配的所有值
问题描述:
对不起,标题有点复杂!在另一个表中返回与另一个表中的一个链接值匹配的所有值
我有一个小的搜索无处不在,但我不知道我措辞它有权得到任何结果,或者是我想要做的,甚至有可能...
基本上我想返回表中的所有值都与另一个值中的值匹配。 我有表:
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
如果我要寻找的只是一个模型的名称(例如保驾护航。)我想这些结果:
Car Model_ID Model
------------------------
Ford 14 Mustang
Ford 21 Focus
Ford 87 Escort
因此搜索某个车型将返回所有的车型这是一样的。
任何人有任何想法?
答
这是你在找什么?
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');
答
您可以使用此查询。如果有多个具有相同名称的模型,则此查询将返回所有与提供的模型名称对应的所有模型。 PostgreSQL的
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 = ?
实例(因为MySQL不支持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)
答
你可以使用子查询检索Car_ID
一个模型:
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'
)
答
试试这个:
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')
答
根据您查看的表格的大小,在子查询中获取信息而不是使用“in”或连接可能会更有效。
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')
是的,这是!我正在尝试类似的东西,无处可去......现在我的工作很好,谢谢你的时间。 – penpen