CODEWITHSUNDEEP
sqlsql-server
Panos
Panos

Reputation: 87

Select query including a link table

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.

I wrote this 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.

schema link

Upvotes: 1

Views: 94

Answers (3)

Javaluca
Javaluca

Reputation: 857

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

Upvotes: 4

Rahul Tripathi
Rahul Tripathi

Reputation: 172418

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:

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'

Upvotes: 1

sgeddes
sgeddes

Reputation: 62841

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

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'

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

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

Upvotes: 3

Related Questions