Geek Answers Handbook

Mysql join the 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 the rows from table1, where the department of agents is equal to that in table2.

I tried several different connection statements, but I either misunderstood the syntax, or simply won't work with this table setting. I am a beginner when it comes to MySQL and from what I read JOIN is at the difficult end of the spectrum!

Another option I considered is to duplicate the "department" column in table1, but this will require a bit more coding on the interface, and I'm trying to figure out if I can achieve the desired result without doing this.

Any help is greatly appreciated.

+3
source share
4 answers

I do not quite understand your question ... Only in table 2 is there a department, the only thing they have is agent_name.

I suspect that you really mean that you want all the rows from table 1, where the agent is from a certain department, is that what you want? In this case, something like this should do it (did not check it):

 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 a string value, you really should consider using the identifier from table2 as the key in table1 (let it be called agent_id maybe) to bind them together, Foreign keys (link between tables) must be a real unique identifier. The department should also be the key. Then it will be:

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

Check this out, this is the best explanation of sql connections I've ever found: Visual explanation of SQL connections (written by someone that the SO user may have heard of);

+1
source

Although I can't figure out exactly what you need and how the tables are related, I would try something like this:

 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

I am currently assuming that you need to associate tables with the agent name.

BTW joins are the easiest end to the SQL spectrum :)

0
source

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

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

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 can use some kind of external connection, so I suggest you take a look at some articles over the Internet.

0
source

More articles:


All Articles