Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 456
  • Last Modified:

SQL Query Joining tables

Hi there,

I have a query that returns the following result:

FullName       ClockTime                 Normal                     Breaks
Person A       2011/04/01                     1                             1
Person A       2011/04/02                     0                             0
Person A       2011/04/01                     0                             0

The query is as follows:

select Distinct Emp.Name + ' ' + Emp.Surname As FullName, C.ClockTime,
SDay.Normal, SDay.Breaks
from Employee Emp
INNER JOIN ClockIns C ON Emp.Emp_Id = C.Emp_ID
INNER JOIN EmpShift ON  EmpShift.Employee_ID=Emp.Emp_ID
INNER JOIN  Rules ON Rules.ShiftRules_ID = EmpShift.ShiftRules_ID
INNER JOIN SDay ON Rules.ShiftRules_ID = SDay.ShiftRules_ID
and SDay.CycleDay= EmpShift.StartDate_CycleDayNo + (day(C.ClockTime - EmpShift.Emp_StartDate) % Rules.SRShiftCycle_Days)-1

Now, I need to join another table, SBreaks to this, but the SBreaks table only stored records where the employee took a lunch break, which is not necessarily every day.  When I try to inner join this table, it only shows me the days when an employee took a break, which is not what I want.  When I outer join it, I get incorrect results.  Any ideas on how to successfully join this table to the query?
0
NerishaB
Asked:
NerishaB
  • 13
  • 8
  • 2
  • +2
2 Solutions
 
Pratima PharandeCommented:
try this

select Distinct Emp.Name + ' ' + Emp.Surname As FullName, C.ClockTime,
SDay.Normal, SDay.Breaks ,SBreaks.colname
from Employee Emp
INNER JOIN ClockIns C ON Emp.Emp_Id = C.Emp_ID
INNER JOIN EmpShift ON  EmpShift.Employee_ID=Emp.Emp_ID
INNER JOIN  Rules ON Rules.ShiftRules_ID = EmpShift.ShiftRules_ID
INNER JOIN SDay ON Rules.ShiftRules_ID = SDay.ShiftRules_ID
Left  join SBreaks ON Emp.Emp_Id = SBreaks.Emp_Id
and SDay.CycleDay= EmpShift.StartDate_CycleDayNo + (day(C.ClockTime - EmpShift.Emp_StartDate) % Rules.SRShiftCycle_Days)-1


Added this

Left  join SBreaks ON Emp.Emp_Id = SBreaks.Emp_Id

and in select  SBreaks.colname

change the col names as per your requirment

0
 
Alpesh PatelAssistant ConsultantCommented:
Use LEft outer join to those table which have data optional

select Distinct Emp.Name + ' ' + Emp.Surname As FullName, C.ClockTime,
SDay.Normal, SDay.Breaks
from Employee Emp
INNER JOIN ClockIns C ON Emp.Emp_Id = C.Emp_ID
INNER JOIN EmpShift ON  EmpShift.Employee_ID=Emp.Emp_ID
INNER JOIN  Rules ON Rules.ShiftRules_ID = EmpShift.ShiftRules_ID
Left Outer JOIN SDay ON Rules.ShiftRules_ID = SDay.ShiftRules_ID
and SDay.CycleDay= EmpShift.StartDate_CycleDayNo + (day(C.ClockTime - EmpShift.Emp_StartDate) % Rules.SRShiftCycle_Days)-1
0
 
NerishaBAuthor Commented:
Thanks guys, I tried left outer join.  It is giving me the results where a person did not have breaks, but it also added some data in the field where the person did have breaks.  In other words, there are zeros in the "Breaks" field for some days where it shouldn't be.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Pratima PharandeCommented:
is there any time field in sBreaks then add this in left join on condition
0
 
NerishaBAuthor Commented:
There is a SBreaks.StartTime and SBreaks.EndTime.  How do I do this?
0
 
Pratima PharandeCommented:
Left  join SBreaks ON Emp.Emp_Id = SBreaks.Emp_Id and   C.ClockTime = SBreaks.StartTime
0
 
NerishaBAuthor Commented:
Actually, no SBreaks has StartTime and EndTime.  ClockTime works only with dates.
0
 
NerishaBAuthor Commented:
Maybe a nested select??? Need help doing that though...
0
 
Alpesh PatelAssistant ConsultantCommented:
Please filter out the unwanted tuples.
0
 
NerishaBAuthor Commented:
I need all of the tuples in the query as the query is quite large.  I only added a small bit of it, becasue if I can get this bit to work, I'll be able to get the entire query to work.
0
 
GhunaimaCommented:
What is the structure of sbreaks, There must be a date field in it & you have to include it in the condtion
0
 
NerishaBAuthor Commented:
I have changed StartTime and EndTime of SBreaks to be of type date.  It still does not work.
0
 
NerishaBAuthor Commented:
There is a bit field on SDay that is true if the employee takes breaks on a particular day and false if they do not take breaks.  So what I'm looking for is the starttime and endtime of all breaks, SBreaks is "0" then I merely want a null represented.
0
 
Pratima PharandeCommented:

how SDay and Sbreaks releated with each other ?
0
 
NerishaBAuthor Commented:
SBreaks is a child to SDay.

SBreaks.SDay_Id = SDay.SDay_Id
0
 
Pratima PharandeCommented:
select Distinct Emp.Name + ' ' + Emp.Surname As FullName, C.ClockTime,
SDay.Normal, SDay.Breaks ,SBreaks.starttime,SBreaks.endtime
from Employee Emp
INNER JOIN ClockIns C ON Emp.Emp_Id = C.Emp_ID
INNER JOIN EmpShift ON  EmpShift.Employee_ID=Emp.Emp_ID
INNER JOIN  Rules ON Rules.ShiftRules_ID = EmpShift.ShiftRules_ID
INNER JOIN SDay ON Rules.ShiftRules_ID = SDay.ShiftRules_ID
Left  join SBreaks ON Emp.Emp_Id = SBreaks.Emp_Id and SBreaks.SDay_Id = SDay.SDay_Id and SDay.bit_field_name=1
and SDay.CycleDay= EmpShift.StartDate_CycleDayNo + (day(C.ClockTime - EmpShift.Emp_StartDate) % Rules.SRShiftCycle_Days)-1

0
 
NerishaBAuthor Commented:
SBreaks is not related to the Employee Table.  I cannot add Emp.Emp_Id = SBreaks.Emp_Id
0
 
Pratima PharandeCommented:
select Distinct Emp.Name + ' ' + Emp.Surname As FullName, C.ClockTime,
SDay.Normal, SDay.Breaks ,
SBreaks.starttime,
SBreaks.endtime
from Employee Emp
INNER JOIN ClockIns C ON Emp.Emp_Id = C.Emp_ID
INNER JOIN EmpShift ON  EmpShift.Employee_ID=Emp.Emp_ID
INNER JOIN  Rules ON Rules.ShiftRules_ID = EmpShift.ShiftRules_ID
INNER JOIN SDay ON Rules.ShiftRules_ID = SDay.ShiftRules_ID
Left  join SBreaks ON SBreaks.SDay_Id = SDay.SDay_Id and SDay.bit_field_name=1
and SDay.CycleDay= EmpShift.StartDate_CycleDayNo + (day(C.ClockTime - EmpShift.Emp_StartDate) % Rules.SRShiftCycle_Days)-1
0
 
Pratima PharandeCommented:
select Distinct Emp.Name + ' ' + Emp.Surname As FullName, C.ClockTime,
SDay.Normal, SDay.Breaks ,
SB.starttime,
SB.endtime
from Employee Emp
INNER JOIN ClockIns C ON Emp.Emp_Id = C.Emp_ID
INNER JOIN EmpShift ON  EmpShift.Employee_ID=Emp.Emp_ID
INNER JOIN  Rules ON Rules.ShiftRules_ID = EmpShift.ShiftRules_ID
INNER JOIN SDay ON Rules.ShiftRules_ID = SDay.ShiftRules_ID
Left  join
( select SBreaks.starttime,SBreaks.endtimeSBreaks From SBreaks where  SBreaks.SDay_Id = SDay.SDay_Id and SDay.bit_field_name=1) SB
where SDay.CycleDay= EmpShift.StartDate_CycleDayNo + (day(C.ClockTime - EmpShift.Emp_StartDate) % Rules.SRShiftCycle_Days)-1
0
 
NerishaBAuthor Commented:
It gives me an error, "Incorrect syntax near 'where'
0
 
Pratima PharandeCommented:
select Distinct Emp.Name + ' ' + Emp.Surname As FullName, C.ClockTime,
SDay.Normal, SDay.Breaks ,
SB.starttime,
SB.endtime
from Employee Emp
INNER JOIN ClockIns C ON Emp.Emp_Id = C.Emp_ID
INNER JOIN EmpShift ON  EmpShift.Employee_ID=Emp.Emp_ID
INNER JOIN  Rules ON Rules.ShiftRules_ID = EmpShift.ShiftRules_ID
INNER JOIN SDay ON Rules.ShiftRules_ID = SDay.ShiftRules_ID
Left  join
( select SBreaks.starttime,SBreaks.endtimeSBreaks From SBreaks where  SBreaks.SDay_Id = SDay.SDay_Id and SDay.bit_field_name=1) SB on SB.SDay_Id = SDay.SDay_Id
where SDay.CycleDay= EmpShift.StartDate_CycleDayNo + (day(C.ClockTime - EmpShift.Emp_StartDate) % Rules.SRShiftCycle_Days)-1
0
 
NerishaBAuthor Commented:
That makes no difference to the result.   I'm still getting a result of SRBreak being 0 at one instance or another.
0
 
GhunaimaCommented:
Try this
 
select Distinct Emp.Name + ' ' + Emp.Surname As FullName, C.ClockTime, 
SDay.Normal, SDay.Breaks, sbreak.starttime, sbreak.endtime
from Employee Emp 
INNER JOIN ClockIns C ON Emp.Emp_Id = C.Emp_ID 
INNER JOIN EmpShift ON  EmpShift.Employee_ID=Emp.Emp_ID
INNER JOIN  Rules ON Rules.ShiftRules_ID = EmpShift.ShiftRules_ID
INNER JOIN SDay ON Rules.ShiftRules_ID = SDay.ShiftRules_ID
and SDay.CycleDay= EmpShift.StartDate_CycleDayNo + (day(C.ClockTime - EmpShift.Emp_StartDate) % Rules.SRShiftCycle_Days)-1
left outer join sbreak on emp.emp_id=sbreak.emp and sbreak.starttime between empShift.emp_startdate and empShift.emp_Endtdate

Open in new window

0
 
NerishaBAuthor Commented:
Nope, there is no join to the Employee table.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
please post the structure of the sbreaks table and explain what is the logic behind the join.
0
 
NerishaBAuthor Commented:
I actually got it figured out, turns out I put the "AND" in the wrong place.  Gonna give the points to pratima, for all her help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 13
  • 8
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now