Solved

MySQL query Syntax with many to many relationship...

Posted on 2009-04-13
4
298 Views
Last Modified: 2012-05-06
I have a table employees and a table shifts.  They have a many to many relationship.

I want to be able to view the employees who are logged into two/three specific shifts.
So the query is kinda like this...

SELECT SHIFTS.EMP_ID,
      SHIFTS.SHIFT_ID,
      EMPLOYEES.EMP_FNAME
FROM EMPLOYEES INNER JOIN SHIFTS ON EMPLOYEES.EMP_ID = SHIFTS.EMP_ID
WHERE SHIFT_ID = (26 AND 27 AND 28)

except the where statement is clearly wrong.  I want it to return the employees who are logged onto the shifts 26 AND 27 AND 28.

Please help with the syntax.  Thanks in advance!
Nick
0
Comment
Question by:Chimeraza
  • 3
4 Comments
 
LVL 14

Expert Comment

by:racek
ID: 24128788
SELECT SHIFTS.EMP_ID,
      SHIFTS.SHIFT_ID,
      EMPLOYEES.EMP_FNAME, count(distinct SHIFT_ID) shifts
FROM EMPLOYEES
JOIN SHIFTS ON EMPLOYEES.EMP_ID = SHIFTS.EMP_ID
WHERE SHIFT_ID IN (26 ,27 ,28)
group by 1,2,3
HAVING count = 3;
0
 
LVL 14

Expert Comment

by:racek
ID: 24128791
oops

SELECT SHIFTS.EMP_ID,
      SHIFTS.SHIFT_ID,
      EMPLOYEES.EMP_FNAME, count(distinct SHIFT_ID) shifts
FROM EMPLOYEES
JOIN SHIFTS ON EMPLOYEES.EMP_ID = SHIFTS.EMP_ID
WHERE SHIFT_ID IN (26 ,27 ,28)
group by 1,2,3
HAVING shifts = 3;
0
 
LVL 14

Accepted Solution

by:
racek earned 500 total points
ID: 24128804
or more info:
SELECT SHIFTS.EMP_ID,
      SHIFTS.SHIFT_ID,
      EMPLOYEES.EMP_FNAME,
      count(distinct IF(SHIFT_ID IN (26 ,27 ,28),SHIFT_ID, NULL)) shifts,
      COUNT( distinct SHIFT_ID) total_shifts
FROM EMPLOYEES
JOIN SHIFTS ON EMPLOYEES.EMP_ID = SHIFTS.EMP_ID
group by 1,2,3
HAVING shifts = 3;

if SHIFTS has UNIQUE EMP_ID + SHIFT_ID, you can remove DISTINCT from count
0
 
LVL 3

Author Closing Comment

by:Chimeraza
ID: 31569439
Thanks, works like a charm!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now