Help for a sql query

[Rksk]

Imagine there are two tables like below.

Table1:

user_id	user_name	group_id
1	neo	admin
2	rksk	promo
3	nipuna	promo
4	face	promo
5	herath	help
6	saman	user
7	magneto	mod
8	x	user
9	y	user
.
.
.

Table2:

post_id	title	text	user_id
.
.
.

I want to select all the posts by all users who has the group_id "promo".
It can do by getting "promo" group members' ids from table1 and selecting all topics made by those ids from table2.

But when those tables being larger this can be a very slower way.

Please tell me a one sql query to do that.

(I'm using PHP & Mysql)

[Neo]

Code: Select all

SELECT * FROM table1, table2 WHERE table1.user_id = tabled2.user_id AND group_id = 'promo';

The * after SELECT will add all fields to your query result which will slow down the operation. So make sure you only add the fields you need in the output.

For example, if you only need user_id, post_id and title,

Code: Select all

SELECT table1.user_id, post_id, title FROM table1, table2 WHERE table1.user_id = tabled2.user_id AND group_id = 'promo';

Note that it is required to use table name as a prefix when fields exists with the same name. If unique, no need to add the table name as a prefix.

[Rksk]

thank you.