i.m trying to do a inner join on two table. 1. users , 2. rides. in user table there are two types of user(0 for user , 1 for driver). and in ride table i store user_id and provide_id which came from users table. so now problem is that i can't get information of both user simultaneously.
SELECT name,mobile, email, name as driverName
FROM users
INNER JOIN rides
ON users._id = rides.user_id
INNER JOIN rides as ride
ON users._id = ride.provider_id;
this return me null table.
| name | number | drivername |
|---|
and when i use only single join :
SELECT name,mobile, email, name as driverName
FROM users
INNER JOIN rides
ON users._id = rides.user_id```
it return me name , mobile and email but not drivername.
| name | number | email |drivername |
|:---- |:------:| -----:|----------:|
| xyz | 000000 | email | |
i want ouput like this
| name | number | email |drivername |
|:---- |:------:| -----:|----------:|
| xyz | 000000 | email | abcsd |
I might be inclined to express your query using exists logic:
SELECT u.name, u.mobile, u.email
FROM users u
WHERE EXISTS (SELECT 1 FROM rides r WHERE u._id = r.user_id) AND
EXISTS (SELECT 1 FROM rides r WHERE u._id = r.provider_id);
This will return every user for whom there is a both a user and provider ride.