mysql join question

Question

Hi,

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

Check this out, it's the best explaination of sql joins I've ever found: A Visual Explanation of SQL Joins (it's written by someone a SO user probably have heard of ;)

Answer 2

Although I cannot exactly understand, what you need and how are tables related, I'd try someting similar:

select 
    a.id, a.agent_name, a.ticket_id, 
    a.category, a.date_logged, b.department
from 
    table1 a inner join table2 b on b.agent_name=a.agent_name

Currently I'm assuming that you need to link tables on agent_name.

BTW, joins are the simplest end of the SQL spectrum :)

Answer 3

You migth want to add a unique relational key among the two tables instead of using the agent name in both. Something like this:

Table1: id, agent_id,  ticket_id, category, date_logged
Table2: agent_id, agent_name, department, admin_status

A SQL example:

SELECT t2.agent_name, t1.date_logged FROM table1 t1
INNER JOIN table2 t2 ON t2.agent_id = t1.agent_id

However you might want to use some kind of outer join so I suggest you take a look at some articles over the Internet.

Answer 4

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';