mysql join question

Question

I have two tables with the following columns:

table1:

id, agent_name, ticket_id, category, date_logged

table2:

id, agent_name, department, admin_status

What I'm trying to achieve is to Select all rows from table1 where an agents department is equal to that of table2.

I've tried a few different join statements but I'm either getting the syntax wrong or it just won't work with this table setup. I'm a beginner when it comes to MySQL and from what I've read JOIN's are at the complex end of the spectrum!

One other option I've considered is duplicating the column "department" into table1 but that will require a little bit more coding on the frontend and I'm trying to see if I can achieve the desired result without doing that.

Any assistance greatly appreciated.

Answer 1

I don't quite understand your question... Only table2 have a department, the only thing they have in common is agent_name.

I do suspect what you really mean is: that you want all rows from Table1 where the agent is from a certain department, is that what you want? In that case, something like this should do it (haven't tested it though):

SELECT * FROM Table1 
      INNER JOIN Table2 
      ON Table1.agent_name = Table2.agent_name 
      WHERE Table2.department = 'somespecific value';

BTW: (Inspired by what someone else said) agent_name sounds like it's a string value, you really should consider using the id from table2 as a key in table1 (let's call it agent_id perhaps) to link them together. The foreign keys (the link between tables) should be a real unique id. The department should pobably allso be a id key. Then it would be:

SELECT * FROM Table1 
      INNER JOIN Table2 
      ON Table1.agent_id = Table2.id 
      WHERE Table2.department = 'somespecific value';