2 Replies - 127 Views - Last Post: 04 December 2018 - 04:43 AM

#1 VrX   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 29-November 18

Left join

Posted 03 December 2018 - 06:51 AM

I have 2 tables, firstly employee teams which will has fields month,year,empno (which are grouped as unique) and further fields with what team, and area they are working in. This is updated monthly but old data remains the idea being you can then find what department someone was working in on any particular month/year.

I then have a performance table which again has fields month,year,empno (which are grouped as unique) and further fields for relevant performance categories.

If I then wanted to call say sales figures for Team A in October and use;

select dqr.sales from dqr left join staff on dqr.empno = staff.empno where staff.team = "Team A" AND dqr.month = "October" AND dqr.year = "2018" group by staff.team;



How would this work if a member of staff was in Team A in September and moved to Team B in October. Would the join match this?

Is there a better way to join the tables? Or should I add in staff.month = "October" etc... to query?

Is This A Good Question/Topic? 0
  • +

Replies To: Left join

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14862
  • View blog
  • Posts: 59,302
  • Joined: 12-June 08

Re: Left join

Posted 03 December 2018 - 07:47 AM

You should be able to test this with a quick temp table or two, right?

Side note - you should keep months as numbers and not words.
Was This Post Helpful? 0
  • +
  • -

#3 VrX   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 29-November 18

Re: Left join

Posted 04 December 2018 - 04:43 AM

They are, just used full spelling in example query :)

MariaDB [aw]> select month from dqr limit 1;
+-------+
| month |
+-------+
|    11 |
+-------+



To answer own question if anyone else ever has same query.

It would match incorrectly and would either need to add in staff.month, staff.year to WHERE query or using an AND in left join so in previous example...

left join staff on dqr.empno = staff.empno AND dqr.month = staff.month AND dqr.year = staff.year


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1