mysql - get rows from two tables using join and sub query -


i have table of persons

person_id , person_name

another table of person_vehicle_relation

pv_id , person_id , vehicle_id, role

i want build query in can list of

pv_id , person_name

where vehicle_id= 3 , role = 'driver'.

i have tried join in following way not working. how can desired data?

select persons.person_name , person_vehicle_relation.pv_id  persons    inner join person_vehicle_relations on persons.person_id = (select person_id person_vehicle_relations vehicle_id = 3 , role= 'driver') 

and error

msg 512, level 16, state 1, line 1 subquery returned more 1 value. not permitted when subquery follows =, !=, <, <= , >, >= or when subquery used expression.

why need subquery/inline view? simple should work.

select p.person_name , pvr.pv_id persons p inner join person_vehicle_relations  pvr    on p.person_id = prv.person_id pvr.vehicle_id = 3    , pvr.role= 'driver' 

the reason why had error because subquery returned multiple persons , single person_id person's can't match multiple persons pvr.

you switch "in" instead of "=" , should work well; join , clause seemed simplest maintain , run.

one joins on pk/fk relationship applies limits in or in having. use subquery/inline views instead of straight join when need aggregation done , m-m relationship artificially inflate aggregation. may use subqueries in cross applies or in exists when don't need data second table. in case needed data both tables join seemed best.


Comments

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

python Tkinter Capturing keyboard events save as one single string -

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -