CODEWITHSUNDEEP
mysql
Ramesh Pareek
Ramesh Pareek

Reputation: 1669

Select corresponding rows from another table for each row in first table in mysql

I have a table called users, another table called withdraws,

How do I select a row for each withdraw request from the user with his bank details which are in the users table.

IMPORTANT : user_name in users table is unique for each row. this is corresponding to the userid in withdraws table

Here is, Users Table

+-----------+-----------------+-------------+
| user_name | bank_ac         | ifsc        |
+-----------+-----------------+-------------+
| H6426012  | 456456 | ifsc6544 |
+-----------+-----------------+-------------+

Withdraws Table

+----------+--------+--------+
| userid   | amount | status |
+----------+--------+--------+
| H6426012 | 300.00 |      1 |
| H6426012 | 150.00 |      1 |
| H6426012 | 200.00 |      1 |
+----------+--------+--------+

Here is the query I am using : 

select withdraws.userid 
     , withdraws.amount
     , users.user_name
     , users.ifsc
     , users.bank_ac 
  from withdraws 
  JOIN users 
    on withdraws.userid = users.user_name 
 where withdraws.status = 1

This is the result I am expecting :

I am expecting this result

+----------+--------+--------++----------+--------+--------+
| userid   | amount | status |user_name   | ifsc | bank_ac |
+----------+--------+--------++----------+--------+--------+
| H6426012 | 300.00 |      1 | H6426012 | ifsc6544 | 456456 |
| H6426012 | 150.00 |      1 | H6426012 | ifsc6544 | 456456|
| H6426012 | 200.00 |      1 | H6426012 | ifsc6544 | 456456 |
+----------+--------+--------++----------+--------+--------+

BUT I am getting this result

+----------+--------+--------++----------+--------+--------+
| userid   | amount | status |user_name   | ifsc | bank_ac |
+----------+--------+--------++----------+--------+--------+
| H6426012 | 300.00 |      1 | NULL      | NULL    | NULL |
| H6426012 | 150.00 |      1 | NULL      | NULL    | NULL|
| H6426012 | 200.00 |      1 | NULL      | NULL    | NULL |
+----------+--------+--------++----------+--------+--------+

Upvotes: 0

Views: 1254

Answers (2)

Hamis Hamis
Hamis Hamis

Reputation: 107

First change user_name to user_id on your users schema/table so as it will not confuse you in future. and then, modify your query like below then it will work fine 

select users.userid as user_id, 
     withdraws.amount as amount,
     withdraws.status as status
     users.ifsc as ifsc
     users.bank_ac as bank_ac
  from withdraws 
  JOIN users 
    on withdraws.userid = users.userid 
 where withdraws.status = 1

Upvotes: 0

Mahdi Khardani
Mahdi Khardani

Reputation: 52

I ran the same query and it worked just fine, I also created the same query but without any joins, and it worked as well... I am using SQL Server Management Studio V17.9.

Upvotes: 1

Related Questions