CODEWITHSUNDEEP
mysqlsqlsql-server
Ans Bilal
Ans Bilal

Reputation: 1067

get rows from two tables using join and sub query

I have a table of Persons

Person_ID , Person_Name

another table of Person_Vehicle_Relation

PV_ID , Person_ID , Vehicle_ID, Role

I want to build a query in which I can get the list of

PV_ID , Person_Name

where Vehicle_ID= 3 and Role = 'Driver'.

I have tried join in following way but it is not working. How can I get my desired data?

Select Persons.Person_Name , Person_Vehicle_Relation.PV_ID
 from Persons  
 inner join Person_Vehicle_relations on Persons.Person_ID = (select Person_ID from Person_Vehicle_relations where Vehicle_ID = 3 and Role= 'driver')

and error was

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Upvotes: 1

Views: 233

Answers (1)

xQbert
xQbert

Reputation: 35323

Why do you need a subquery/inline view? a simple where should work.

SELECT P.Person_Name , PVR.PV_ID
FROM Persons P
INNER join Person_Vehicle_relations  PVR
   on P.Person_ID = PRV.Person_ID
WHERE PVR.Vehicle_ID = 3 
  and PVR.Role= 'driver'

The reason why you had an error is because the subquery returned multiple persons and a single person_ID from person's can't match multiple persons from PVR.

You could switch it to "IN" instead of "=" and it should work as well; but a join and a where clause seemed the simplest to maintain and run.

One normally joins on the PK/FK relationship then applies limits in the where or in a having. I generally only use subquery/inline views instead of a straight join when I need aggregation done and a M-M relationship would artificially inflate the aggregation. I may use subqueries also in cross applies or in exists when I don't need data from the second table. In this case you needed data from both tables so a join seemed the best.

Upvotes: 3

Related Questions