选择包含链接表的查询

I got a problem with a select query

我遇到了一个选择查询的问题

I need to select coid,model,km,year for all the vehicles which have AC and MP3.

我需要为所有有AC和MP3的车辆选择coid,model,km,year。

I wrote this SQL:

我写了这个SQL:

select distinct 
    vehicle.vehid, model, km, year
from 
    vehicle, models, extras, veh_extras
where 
    models.modid = vehicle.modid 
    and vehicle.vehid = veh_extras.vehid
    and extras.extraid = veh_extras.extraid 
    and (descr = 'AC' or descr = 'mp3')  

but I think it's wrong. Extras.desc is the column which take the description of the extra.

但我觉得这是错的。 Extras.desc是描述额外内容的列。

schema link

3 个解决方案

#1

SELECT v.vehid, m.model, v.km, v.year
FROM vehicle v 
JOIN model m ON v.modid = m.modid
WHERE EXISTS (  SELECT 'a'
                FROM extras e 
                JOIN veh_extras ve ON e.id = ve.extraid
                WHERE ve.vehid = v.vehid
                AND e.descr = 'AC'
             )
AND EXISTS (    SELECT 'a'
                FROM extras e 
                JOIN veh_extras ve ON e.id = ve.extraid
                WHERE ve.vehid = v.vehid
                AND e.descr = 'mp3'
             )

This is probably not the best way... but if you need to search for more extras simply add another EXISTS condition

这可能不是最好的方式......但如果你需要搜索更多的额外内容,只需添加另一个EXISTS条件

#2

Assuming you want vehicles that have both AC and MP3, then one option would be to join to the veh_extras table multiple times:

假设您想要具有AC和MP3的车辆,那么一个选项是多次加入veh_extras表:

select distinct v.vehid, m.model
from vehicle v
  join models m on m.modid = v.modid
  join veh_extras ve on v.vehid = ve.vehid
  join extras e on ve.extraid = e.extraid and e.descr = 'AC'
  join veh_extras ve2 on v.vehid = ve2.vehid
  join extras e2 on ve2.extraid = e2.extraid and e2.descr = 'MP3'

• SQL Fiddle Demo

SQL小提琴演示

---

Another option would be to use case aggregation:

另一种选择是使用案例聚合:

select v.vehid, m.model
from vehicle v
  join models m on m.modid = v.modid
  join veh_extras ve on v.vehid = ve.vehid
  join extras e on ve.extraid = e.extraid 
group by v.vehid, m.model
having sum(case when e.descr = 'AC' then 1 else 0 end) > 0
  and sum(case when e.descr = 'MP3' then 1 else 0 end) > 0

• More Fiddle

BTW -- I left out some of the remedial columns -- easy to add those back...

顺便说一句 - 我遗漏了一些补救栏目 - 很容易将这些列添加回来......

#3

but i think its wrong.

但我认为这是错误的。

Even if it works then also it is not the recommended way.

即使它工作,那么它也不是推荐的方式。

Try to avoid comma seperated JOINS. Instead try to use JOINS like this:

尽量避免使用逗号分隔的JOINS。而是尝试像这样使用JOINS:

select distinct v.vehid,model,km,year
    from vehicle v inner join models m on v.modid = m.modid 
                 inner join veh_extras ve on ve.vehid = v.vehid
                 inner join extras e on e.extraid = ve.extraid          
        where  
            e.descr='AC' or e.descr='mp3'