CODEWITHSUNDEEP
sql
xiongbenxiong
xiongbenxiong

Reputation: 35

can I use subquery in the inner join condition sql

table (Trips) 

| Id | Client_Id | Driver_Id | City_Id |     Status      |Request_at|


| 1  |     1     |    10     |    1    |     completed      |2013-10-01|

| 2  |     2     |    10     |    1    |     completed      |2013-10-02|

| 3  |     3     |    11     |    6    |     completed      |2013-10-03|

table ( users)

Users_Id    | Banned |  Role  |

|    1     |   No   | client |

|    2     |   Yes  | client |

|    3     |   No   | client |

|    4     |   No      | driver|

How can I join these 2 tables (trips, users) on the condition that the user role needs to be client and these clients should be unbanned. I am trying to use a subquery but I got "single-row subquery returns more than one row"error and not sure what went wrong. 

SELECT * FROM Trips
INNER JOIN users
ON client_id = (SELECT users_id from users 
                  where 
                  role = 'Client' and  banned = 'No');

Upvotes: 0

Views: 48

Answers (4)

Will Irwin
Will Irwin

Reputation: 31

Try

SELECT * FROM Trips
INNER JOIN users ON users.users_id = Trips.Client_ID
WHERE users.Banned = 'No' AND users.Role = 'client'

Upvotes: 0

Ry.the.Stunner
Ry.the.Stunner

Reputation: 22

 SELECT *
 FROM Trips
 INNER JOIN Users ON [Trips].Id = [Users].User_Id AND [Users].Role = 'client' AND [Users].Banned = 'No'

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

You don't need the subquery
the join should be between Trips and Users eg :

SELECT * 
FROM Trips
INNER JOIN users ON Trips.client_id = usres.users_id 
        and users.role = 'Client' 
        and  users.banned = 'No';

Upvotes: 0

user330315
user330315

Reputation:

No need for a sub-select, just put those conditions into the join condition. 

SELECT * 
FROM Trips
  JOIN users ON trips.client_id = users.users_id 
            and users.role = 'Client' 
            and users.banned = 'No';

As that is an inner join, this is equivalent to:

SELECT * 
FROM Trips
  JOIN users ON trips.client_id = users.users_id 
WHERE users.role = 'Client' 
  and users.banned = 'No';

This would not be different if you had used an outer join

Upvotes: 2

Related Questions