Jump to content

Get All Records From Table one Joining other table


lilmer
Go to solution Solved by Barand,

Recommended Posts

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

 

 

Link to comment
Share on other sites

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.  

Link to comment
Share on other sites

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
  • Like 1
Link to comment
Share on other sites

  • Solution

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.

  • Like 2
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.