CODEWITHSUNDEEP
mysql
Mithu
Mithu

Reputation: 655

How to Merge Two Select Query in Mysql

I have below table structure in my users table where user and driver are saved with a unique id and separated with user_type column. user ratings are being saved in rider_avg_ratings column and driver ratings are being saved in driver_avg_ratings column.

enter image description here

when a user submit a request it is being saved in requesttable with both userid and nearby driver id. now I have to return both driver ratings and user ratings from users table in a single query.Problem is when I join request.userid=users.id it is returning rider_avg_ratings to get the driver_avg_ratings i need to join users.id=request.driver_id how can I get both user and driver ratings from a single query

enter image description here

From above two table by joinning request.user_id=users.id I need to return driver_avg_ratings=4.38 and rider_avg_ratings=1.25

Upvotes: 0

Views: 1024

Answers (3)

Mansi
Mansi

Reputation: 153

SELECT r.user_id as userId, u.rider_avg_ratings as ratings 
FROM user as u 
INNER JOIN request as r on u.id = r.user_id 

UNION 

SELECT r1.driver_id as userId, u1.driver_avg_ratings as ratings 
FROM user as u1 
INNER JOIN request as r1 on u1.id = r1.driver_id

This query will fetch the desired result.

Upvotes: 1

Muhammad Ali
Muhammad Ali

Reputation: 1

for example: Department 1 A 2 B 3 A 4 C 5 B 6 D 7 E 8 F

You could do something like

SELECT 1 AS Deptnumber , Dept FROM tbl_students WHERE Dept IN ('A', 'B', 'C') UNION SELECT 2 AS DeptNumber , Dept FROM tbl_students WHERE Dept IN ('D', 'E') UNION SELECT 3 AS Deptnumber , Dept FROM tbl_students WHERE Dept IN ('F')

Upvotes: 0

ysth
ysth

Reputation: 98388

Your union query should produce a The used SELECT statements have a different number of columns error. You need to figure out why your code isn't checking for errors and make it do so. Also, if this is the entire statement, the parentheses around it don't belong.

You need to make the two unioned selects return the same number of parameters. Note that as in the second select's select columns will be ignored and result column names will come from the first select only. Minimally, to make the query run, you would need to add ,NULL,NULL,... to your second select, but it seems likely you want more information to be able to identify which user the driver_avg_ratings is for, like the id, name, etc the first query is returning.

It's often helpful when processing the results to add something indicating which select a row came from, too, e.g. SELECT "pending_request_users" AS type, ... UNION ALL SELECT "all_users_avg_rating", ....

Note that the different SELECTs unioned together return entire rows. If your only goal is to add driver ratings to the rows already returned, you don't want a union, you may just want to add:

SELECT ..., driver.driver_avg_ratings
FROM user u
...
INNER JOIN user AS driver ON driver.id=req.driver_id

(or left join if there may not be a driver_id). But it's hard to tell for sure if that's what you want. Provide sample data and your desired results from it. In any case, do make your code detect errors properly.

Upvotes: 0

Related Questions