How to perform inner join in mysql

Question

I have to write a query such that ,I need to get events whose start date is of 30 min from now. My conditions are:

1) get the event from events table

2)Join created by of events with id in users table.

3)Comments from comment table with user ser id

But the problem here is if there is no comment for event then the event it self is not coming.If any comment is present it is coming.I dont want this.If comment is not there just fetch it as empty but not hide the total event .Can anyone please help me,.Thanks.

 select u.email ,group_members.user_id,users.first_name,u.first_name
                as host_name,events.name,events.start_date,comments.comments,c.first_name as 
                comment_user,comments.id from events 
                inner join users as u on u.id = events.created_by
                inner join comments on events.id = comments.event_id
                inner join group_members on events.group_id = group_members.group_id
                inner join users as c on comments.from_user = c.id
                inner join users on group_members.user_id = users.id
                where events.start_date  between date_add(now(),interval 1 minute) and date_add(
                now(),interval 30 minute)
                and group_members.user_status = 2
                and events.status = 2

use **LEFT JOIN** change it to **LEFT JOIN comments on events.id = comments.event_id** — Bernd Buffen, Mar 31 '17 at 11:15
Not possible with `inner join` … that's the point of `left/right/full outer join`. — Disillusioned, Mar 31 '17 at 11:15
Possible duplicate of [What is the difference between "INNER JOIN" and "OUTER JOIN"?](http://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — Disillusioned, Mar 31 '17 at 11:17
This looks a bit queer. You can have several comments and several group members per event it seems and you cross join both. So with 12 comments and 7 group members for an event you'd show 84 records with all those comments and members shown again and again. Is this desired? — Thorsten Kettner, Mar 31 '17 at 12:03

score 1 · Answer 1 · answered Mar 31 '17 at 11:22

You need a left join to the comments table. I would put that table last in the from clause.

select u.email, gm.user_id, gu.first_name, u.first_name as host_name,
       e.name, e.start_date, c.comments, uc.first_name as comment_user,
       c.id 
from events e inner join
     users u
     on u.id = e.created_by inner join
     group_members gm
     on e.events.group_id = gm.group_id inner join
     users gu
     on gm.user_id = gu.id left join
     comments c
     on e.id = c.event_id left join
     users uc
     on c.from_user = uc.id
where e.start_date between date_add(now(),interval 1 minute) and date_add(now(),interval 30 minute) and
       gm.user_status = 2 and
       e.status = 2;

Once you use a left join on comments, you also need a left join for the from user. I replaced all table names with aliases -- this makes it easier to track which table is used for which purpose.

score 0 · Answer 2 · answered Mar 31 '17 at 11:21

0

Use the INNER JOIN Keyword and select the two columns by putting them with keyword ON.

SELECT EMP.EMP_ID, EMP.EMP_NAME, DEPT.DEPT_NAME FROM EMP
INNER JOIN DEPT ON DEPT.DEPT_ID = EMP.DEPT_ID;

answered Mar 31 '17 at 11:21

Abhi

104
5

Did you bother to read the question; the actual text of the question and not just the question title? – Disillusioned Mar 31 '17 at 15:21

How to perform inner join in mysql

2 Answers2