Get All Records From Table one Joining other table

[lilmer]

I've got two tables

 

user table
- id

- username

- fname

- lname

- group_id

 

event Table

- id

- user_id

- event_time

- event_date

- event ('Arrival','Departure','Break',etc..)

My problem is getting all user by group and joining other table even date, but it should be base on 'event_date' 
I use `LEFT JOIN` and `LEFT OUTER JOIN` but because I have to specify the `event_date` not all user can be shown because some are absent.

I made this

SELECt * FROM 
user as u
LEFT OUTER JOIN event as e
ON u.id = e.user_id
WHERE
u.group_id = 6
AND ( e.event_date = '2016-07-05' AND e.event = 'Arrival' )
GROUP BY u.id

but it only show the user who has an event on the date specified.  I tried to make an VIEW table but it still has the condition base on event_date.

Although I made this on PHP by condition but it takes time to load as I select event on every user.

is there a way to get it using SQL?
 

it should be like this:

 

 

user1 , Arrival , 10:44:35 , '2016-07-06',

user2 , null    , --:--:-- , 0000-00-00

user3 , Arrival , 10:04:22 , '2016-07-06',

user4 ,  null    , --:--:-- , 0000-00-00

 

 

[Muddy_Funster]

I don't understand, if event_date is irrelevant why to you have to use it? And if you want users with event "null" why do you include e.event="Arrival" in your where clause?

[lilmer]

The main point is to get all the user..  and base on the event date it can be check user status, like sample image date 2016-07-05

[Muddy_Funster]

so you want all users, regardless of whether they have an entry relating to the date you enter, and you want to mark the ones that don't have an entry for that date as absent.  Is that right?

[lilmer]

Exactly, sorry if my question is hard to understand. 

 

 

 you want to mark the ones that don't have an entry for that date as absent. 

so none, it can be null, I can make a condition on client side on that.  

[Muddy_Funster]

Well I don't know what the e.event = "Arrival" is for, but the only way I can think of to do what you are trying is with a subquery join. Something like the following:

 

SELECT CONCAT(fname,' ',lname) AS fullName, event_time, event_date, event
FROM user_table AS u
LEFT JOIN
(
  SELECT uid, event_time, event_date, event
  FROM event_table AS ev
  WHERE event_date = '2016-07-05'
) AS e
ON u.id = e.uid

[Barand]

When you LEFT JOIN to a table then any conditions regarding its data cannot be in the WHERE clause, they must be in the ON conditions in the join.

SELECT * FROM 
user as u
LEFT OUTER JOIN event as e
ON u.id = e.user_id AND  e.event_date = '2016-07-05' AND e.event = 'Arrival' 
WHERE
u.group_id = 6
GROUP BY u.id

And don't use SELECT *. Specify the columns you want.

[lilmer]

Wow, I didn't know that. I thought it is just use for relationship. Thank you guys

[Barand]

You specify the records you want to LEFT join to.

 

If you put those conditions in the WHERE clause, as you had originally, then the LEFT join behaves like an INNER JOIN.