lilmer Posted July 6, 2016 Share Posted July 6, 2016 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 Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted July 6, 2016 Share Posted July 6, 2016 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? Quote Link to comment Share on other sites More sharing options...
lilmer Posted July 6, 2016 Author Share Posted July 6, 2016 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 Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted July 6, 2016 Share Posted July 6, 2016 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? Quote Link to comment Share on other sites More sharing options...
lilmer Posted July 6, 2016 Author Share Posted July 6, 2016 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. Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted July 6, 2016 Share Posted July 6, 2016 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 1 Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted July 6, 2016 Solution Share Posted July 6, 2016 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. 2 Quote Link to comment Share on other sites More sharing options...
lilmer Posted July 6, 2016 Author Share Posted July 6, 2016 Wow, I didn't know that. I thought it is just use for relationship. Thank you guys Quote Link to comment Share on other sites More sharing options...
Barand Posted July 6, 2016 Share Posted July 6, 2016 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.