fetch records from four different table using join query - SEO

UmangPatoliya
August 13, 2015
166 views
2 votes
3 Answers

Table : Post

id       user_id     group_id    Text
1         1            1         AAA
2         2            2         BBB
3         2            2         CCC
4         3            2         DDD
5         2            3         EEE

Table : UserMaster

user_id     username   
1           Raj 
2           Meet
3           Kishan

Table : Groups

group_id     groupname
1            JAVA
2            PHP
3            SEO

Table : JoinRequest

id      user_id     group_id    status
1        1           3          deactive
2        2           2          active
3        2           1          active
4        1           3          active

My Query Here:

//Help me improve my query to get the desired result.
select  * FROM Post  
where status = active in JoinRequest table

and user_id=’2′

Expected Result :

id     username       Groupname     Text     
1      Raj             JAVA         AAA
2      Meet            PHP          BBB
3      Meet            PHP          CCC
4      Kishan          PHP          DDD

Answers

select p.id, u.username, g.groupname, p.text
from posts p
join userMaster u on u.user_id = p.user_id
join groups g on g.group_id = p.group_id
join joinRequest r on r.user_id = p.user_id 
                  and r.group_id = p.group_id
where r.status = 'active'

Select p.id, um.username, g.Groupname, p.Text
From Post as p
JOIN UserMaster as um
  ON p.user_id = um.user_id
JOIN Groups as g
  ON p.group_id = g.group_id
JOIN JoinRequest as jr
  ON p.user_id = jr.user_id
  AND p.group_id = jr.group_id
WHERE jr.status = 'active'

You can get your result like this.

SELECT P.id,
       U.username,
       G.Groupname,
       P.Text
FROM   Post P
       INNER JOIN UserMaster U
               ON U.user_id = P.user_id
       INNER JOIN Groups G
               ON G.group_id = P.group_id
       INNER JOIN JoinRequest JR
               ON P.user_id = JR.user_id
                  AND P.group_id = JR.group_id
WHERE  JR.status LIKE 'active';

Please signup or login to give your own answer.

Click here to cancel reply.

fetch records from four different table using join query – SEO

Answers