MySQL query problems

Question

I have the next 2 tables:

     Users
+----+-----------+
| id | user_name |
+----+-----------+
| 18 | Andrei    |
| 19 | Gicu      |
| 20 | Gigel     |
+----+-----------+
         Requests
+----+-----------+---------+
| id | from_user | to_user |
+----+-----------+---------+
|  3 |        18 |      19 |
|  4 |        18 |      20 |
|  5 |        20 |      19 |
+----+-----------+---------+

And i make the following query:

SELECT requests.from_user 
 FROM requests 
  WHERE 
   (SELECT id FROM users WHERE users.user_name='Gicu')=requests.to_user;

which returns:

+-----------+
| from_user |
+-----------+
|        18 |
|        20 |
+-----------+

My question now is ... how to get the user names associated to these ids (18 and 20) What should i add to the query? (I'm not familiar to joins/union and those other stuff)

Andrew · Accepted Answer · 2012-02-05 19:37:59Z

3

SELECT
  from.id, from.user_name
FROM Requests AS r
JOIN Users AS from ON from.id = r.from_user
JOIN Users AS to ON to.id = r.to_user
WHERE to.user_name LIKE 'Gicu';

Using a subquery should be a sign to you that something is wrong. It's rarely the best way for a beginner to get something done. This ( http://www.sql.co.il/books/tsqlfund2008/ ) is a very good book for beginners and though it's written for MS SQL, 98% of it applies to MySQL as well.

answered Feb 5, 2012 at 19:37

Andrew

4,62429 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Skyrel · Accepted Answer · 2012-02-05 19:44:51Z

2

SELECT users.user_name
FROM users
WHERE users.id IN 
        (SELECT requests.from_user 
         FROM requests 
         WHERE 
              (SELECT id 
               FROM users 
               WHERE users.user_name='Gicu')
         =requests.to_user 
        )

answered Feb 5, 2012 at 19:44

Skyrel

2263 silver badges12 bronze badges

Comments

Gigi · Accepted Answer · 2012-02-05 19:45:18Z

2

SELECT users.user_name FROM users WHERE (SELECT users.id FROM users)=
  (SELECT requests.from_user 
   FROM requests 
   WHERE (SELECT id FROM users WHERE users.user_name='Gicu')=requests.to_user
  )

edited Feb 5, 2012 at 19:45

answered Feb 5, 2012 at 19:34

Gigi

4,96828 silver badges28 bronze badges

2 Comments

AndreiBogdan Over a year ago

it's give me an error: "ERROR 1242 (21000): Subquery returns more than 1 row"

Gigi Over a year ago

you are right, i've updated the answer to support more than a result

Steven Leimberg · Accepted Answer · 2012-02-05 19:35:14Z

1

Try this:

Select u.user_name
from Users u
where u.id = (
    SELECT requests.from_user 
    FROM requests 
    WHERE (SELECT id FROM users WHERE users.user_name='Gicu')=requests.to_user
);

answered Feb 5, 2012 at 19:35

Steven Leimberg

7851 gold badge4 silver badges19 bronze badges

1 Comment

AndreiBogdan Over a year ago

ERROR 1242 (21000): Subquery returns more than 1 row

Ajay Kadyan · Accepted Answer · 2012-07-10 07:49:23Z

1

you can try this.

SELECT u.user_name
FROM users u
WHERE u.id IN 
    (SELECT r.from_user 
     FROM requests r
     WHERE 
          (SELECT id 
           FROM users 
           WHERE user_name='Gicu')
     =r.to_user 
    )

answered Jul 10, 2012 at 7:49

Ajay Kadyan

1,0893 gold badges13 silver badges36 bronze badges

Collectives™ on Stack Overflow

MySQL query problems

5 Answers 5

Comments

Comments

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related