nested mysql query with double select statement?

Question

I have a simple problem regarding a nested query. To be honest I don't know if it can be done through one query only or if I'll have to use PHP.

Put simply, I want to return the users information from the users tables by the users IDs returned from a select statement in relations table.

I can do this by 2 queries and some PHP loop but for saving resources, but I think it's better to combine it into 1 query and single loop.

First query

SELECT UserID FROM relations WHERE GroupID = '1'

Second query I need to retrieve the user info from the user table by the returned UsersIDs from the first select statement.

I can do this by loop through the ID and making the queries but I think I can get all in 1 query.

Thanks

You should use MYSQL JOINS for your requirement. Thanks!

Mohit Singh
– Mohit Singh

2012-07-18 11:18:48 +00:00
Commented Jul 18, 2012 at 11:18 — Mohit Singh
– Mohit Singh, Commented Jul 18, 2012 at 11:18

Roland Bouman · Accepted Answer · 2012-07-18 11:21:12Z

5

This is the typical way to do that:

SELECT     users.*
FROM       users
INNER JOIN relations
ON         users.id = relations.userid
WHERE      relations.groupid = 1

I noticed you were using quotes around the 1 in your query. I am assuming group id is an integer value, in which case you should not use quotes.

The answers that use an IN subquery are likely to be less performant. Esp. with MySQL, the JOIN should always we the preferred way to combine results from tables, since MySQL has particularly lackluster subquery implementation.

answered Jul 18, 2012 at 11:21

Roland Bouman

32.1k8 gold badges69 silver badges69 bronze badges

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

Comments

bugwheels94 · Accepted Answer · 2012-07-18 11:18:45Z

3

Select * from user_table where id in(SELECT UserID FROM relations WHERE GroupID = '1')
                                   OR
Select * from user_table u INNER JOIN relations r ON u.UserID=r.UserID WHERE r.GroupID='1'

answered Jul 18, 2012 at 11:18

bugwheels94

32.1k3 gold badges43 silver badges62 bronze badges

Comments

Madhivanan · Accepted Answer · 2012-07-18 11:18:26Z

2

select * from user_typw 
where userID in (SELECT UserID FROM relations WHERE GroupID = '1')

answered Jul 18, 2012 at 11:18

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Comments

Joe G Joseph · Accepted Answer · 2012-07-18 11:18:47Z

1

try this

select * from user where UserID  in
(SELECT UserID FROM relations WHERE GroupID = '1')

or

select * from user U where exists 
(SELECT * FROM relations R WHERE U.UserID=R.UserID and  R.GroupID = '1')

or

select U.*
from user U join relations R 
on U.UserID=R.UserID 
where  R.GroupID = '1'

answered Jul 18, 2012 at 11:18

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

Comments

Manatok · Accepted Answer · 2012-07-18 11:20:02Z

0

SELECT * FROM user_table ut LEFT JOIN relations r on ut.UserID=r.UserID where r.GroupID=1;

answered Jul 18, 2012 at 11:20

Manatok

5,7263 gold badges26 silver badges32 bronze badges

Comments

Mohit Singh · Accepted Answer · 2012-07-18 11:21:32Z

0

SELECT A.field1,B.field2 FROM table1 A LEFT JOIN table2 B ON A.common_ID=B.common_ID WHERE field = 'abc'

answered Jul 18, 2012 at 11:21

Mohit Singh

1429 bronze badges

Collectives™ on Stack Overflow

nested mysql query with double select statement?

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related