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

Answer 1

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.

Answer 2

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;