0

I know this is possible I just dont know how to the code or how to phrase it to search for the answer I need. I am pretty sure I need to use JOIN but again I am finding it hard to find an example to get the correct syntax.


I have 3 tables. An events table, which contains events that occur on different machines and their times. So for questions purposes events had 4 fields:
EVENTS

  • event_id (primary key, auto_increment)
  • machine_id (foreign key)
  • time_in
  • time_out

  • I then have a table "machines" which lists all of the machines:
    MACHINES

  • id (key)
  • name
  • department_id (foreign key)

    • then lastly i have the Department table:
    DEPARTMENTS

  • id (key)
  • name

    • I want to select all events that occur in a department. So say i want all events from events where the department_id of the machine the event occured on is = 1.

    Ryan McDevitt
    • 73
    • 1
    • 11
    • Which "event occurred on" are you referring to? Is not in the list of fields of any of your tables – Icarus Dec 27 '11 at 23:05
    • if you could get me a list of event_id's that occured on a machine that would be perfect. There is a lot of other math going on is unrelated the the filter. The bigger picture is i am reporting the amount of time report in each department. The departments are defined by the machines in them. So the events table is written to with a machine_id with a timestamp. I find the timediff of the timestamps on a machine and i can get total time. What i am trying to do get just the time reported for a single department with id=1 – Ryan McDevitt Dec 27 '11 at 23:11

    4 Answers4

    4
    SELECT E.event_id, E.machine_id, E.time_in, E.time_out
FROM EVENTS E
INNER JOIN MACHINES M ON E.machine_id=M.machine_id
WHERE M.department_id = 1

    By the way, if you don't know the department id but know the name of the department, you can do an additional JOIN and use the department name:

    SELECT E.event_id, E.machine_id, E.time_in, E.time_out
FROM EVENTS E
INNER JOIN MACHINES M ON E.machine_id=M.machine_id
INNER JOIN DEPARTMENTS D ON M.department_id=D.department_id
WHERE D.Name = 'Accounting'
    Sam
    • 9,933
    • 12
    • 68
    • 104
    3

    You can use this

    SELECT event_id, events.machine_id, time_in, time_out
FROM events 
     INNER JOIN machines ON events.machine_id = machine.id
     INNER JOIN departments ON machines.departments_id = departments.id
WHERE departments.id = 1

    Note that I don't need to join to departments but for performance reason you should, this way you are always searching against a primary key, which has a clustered index and therefore a lot faster

    Bassam Mehanni
    • 14,796
    • 2
    • 33
    • 41
    2
    SELECT e.* FROM events AS e, machines as m 
WHERE e.machine_id = m.id AND department_id = x;

    or if you have the department name

    SELECT e.* FROM events AS e, machines as m, departments as d 
WHERE e.machine_id = m.id AND m.department_d = d.id AND d.name = x;

    This is a Cartesian product method. You can also use inner joins (other answers). Inner Joins are more readable and better ANSI syntax. For more info about inner joins vs Cartesian product

    Community
    • 1
    • 1
    Nate
    • 1,889
    • 1
    • 20
    • 40
    0

    Use a join.

    SELECT events.* FROM events INNER JOIN machines ON machines.id = events.machine_id WHERE department_id = 1

    tdammers
    • 20,353
    • 1
    • 39
    • 56