Mysql Insert query with multiple where and join operations

Question

Need to insert into the table public_holidays, by the given date that matches holidays table date and it should match CLOCK table clock_in and clock_out time fields and fetch time difference in seconds for 28800 = 8hrs and also need to match designation table from users table user_id, sorry for a very messy code, greatly confused any help could be very helpful thank you.

Clock Table

id  user_id     date        clock_in                clock_out             created_at          updated_at
6     12     2016-06-10  2016-06-10 06:00:00    2016-06-10 14:00:00   2016-06-10 19:20:41   2016-06-10 00:15:51

Users

 id         first_name      last_name    designation_id   email
  1          crysis            name           1          [email protected]

holidays

id    date       holiday_description    created_at         updated_at
1    2016-06-10      christmas      2016-06-11 15:23:54   2016-06-11 15:23:54

Designation

designation_id  department_id   designation     created_at       updated_at
1                 1              employee   2016-01-30 21:03:24 2016-01-30 22:03:24

Department

department_id   department_name  department_description     created_at            updated_at    
   1                IT             Info tech             2016-01-30 21:03:24       2016-01-30 21:03:24

Need to insert into this table Public_holidays in the below format by calculating time 8hrs from clock table

user_id     department_id   designation_id  date_cur       clock_in         clock_out        created_at                   updated_at
  12            1                1         2016-06-13   2016-06-10 06:00:00 2016-06-10 14:00:00   2016-06-13 21:03:24       2016-06-13 21:03:24

Tried Messy code

INSERT INTO public_holidays (user_id, department_id,designation_id,clock_in,clock_out) SELECT(cl.user_id, 
   di.department_id, 
   di.designation_id,
   cl.clock_in,
   cl.clock_out)
FROM clock cl, designations di
where
(
select h1.date AS ph_date from holidays h1 WHERE ph_date = 2015-01-22
  AND
select (TIMESTAMPDIFF(second,cl.clock_in, cl.clock_out)=28800) AS differ1
AND
INNER JOIN users AS ui ON ui.designation_id = di.id 
);

Your code is structurally and semantically wrong .. you shoud first start from the table you need and the relation between these table and then define where condition .. last the select column — ScaisEdge
– ScaisEdge, Commented Jun 11, 2016 at 18:28
MySQL hates subqueries, always try to join wherever possible, even if you are joining to your subquery. And the golden rule is, if you are joining on a column, you will need an index on the column.. — RJJ
– RJJ, Commented Jun 11, 2016 at 18:38
Forget about INSERT untill you get your SELECT right. Test queries in some db manager (even phpMyAdmin) to get right result set (without repeated rows). All tables used in select statemetnt should be related somehow, JOINed on that relation and used either directly for data or for filtering result on its fields in WHERE clause or relation itself. (It's possible to use subquery within IN() clause, but it makes a mess). hollidays table is not related to the query. Syntax also wrong - both with users should be joined to selected tables and those are not related either. RTFM. — shudder
– shudder, Commented Jun 12, 2016 at 1:32
@shudder can u show construct an sample query like tat thk u. — Crysis
– Crysis, Commented Jun 12, 2016 at 14:09

shudder · Accepted Answer · 2016-06-13 07:57:58Z

I still don't get the idea, but at least see some relations. Query below will return dataset for all emplyees and all holidays for each of them (h.date column should go into cur_date table). The last JOIN has no relation thus it creates cartesian product - attaches each holiday for each already constructed row (total rows = nuber of empl x number of holidays)

SELECT cl.user_id, di.department_id, di.designation_id, h.date, cl.clock_in, cl.clock_out
FROM clock cl
INNER JOIN users u ON cl.user_id = u.user_id
INNER JOIN designation di ON u.designation_id = u.user_id
CROSS JOIN holidays h --cartesian product: all dates for each row

Don't know what clock table is - standard working hours (one row per employee - I assumed that this is true), daily timesheet (one row per date per employee) or sth else? If its standard working hours then why datetime and not time? If its daily timesheet how should I decide which 8 hrs from that table should you choose (and why 8 hrs)?
Dont know why you calculated/searched for these 8 hrs - do you want to insert holidays for empl. that work 8 hrs only (according to "clock"/in any day)
Don't know what cur_date is (assumed the holiday), but why use date next to datetime - shouldn't be either time or date or datetime only?

Crysis · Accepted Answer · 2016-06-13 08:30:47Z

0

Finally this one works @shudder thanks for the help

INSERT INTO public_holidays (user_id, department_id,designation_id,date_cur,clock_in,clock_out)
SELECT cl.user_id, des.department_id , us.designation_id, cl.date,cl.clock_in, cl.clock_out 

FROM clock cl 

INNER JOIN holidays AS hol ON hol.date = cl.date

INNER JOIN users AS us ON cl.user_id = us.id

INNER JOIN designations AS des ON des.id = us.designation_id

WHERE date(cl.created_at) = '2016-06-13'

AND TIMESTAMPDIFF(second,cl.clock_in, cl.clock_out) = 28800;

answered Jun 13, 2016 at 8:30

Crysis

4162 gold badges7 silver badges28 bronze badges

Collectives™ on Stack Overflow

Mysql Insert query with multiple where and join operations

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related