CODEWITHSUNDEEP
phpmysqldatabase
Tim Fowler
Tim Fowler

Reputation: 11

MySQL:Select a table (for email) based on another tables data

Here is the issue: I have 4 tables in a DB. one is "calls" and the other 3 are the support teams "IT","Maintenance","Engineering". When a row is created in the "calls" table there is a field named" Support team" and there are 3 possible options for this field it, maintenance, and engineering. I need to be able to email these teams based on what team has been requested in the "calls" table. All of the email info is stored in the individual team's table. I hope this makes sense. If not I can diagram the issue.

Upvotes: 1

Views: 106

Answers (2)

Ebuka
Ebuka

Reputation: 634

Create 3 string variables to go into support_team field of the calls table: it, maintenance, engineering. Let the variables have the same name as the database tables it, maintenance and engineering. After inserting a new record in the calls table, use this

$team = "Select calls.support_team from calls where id = $last_id ";
"Select * from $team ";

Where $last_id is the id of the recently inserted record in the calls table. Since $team gets the name of the tables: id, maintenance or engineering; the second line of code just gets the names of team members.

Upvotes: 1

Ja͢ck
Ja͢ck

Reputation: 173562

Since the tables are limited you could do a bunch of left joins:

SELECT * FROM calls
LEFT JOIN team_it ON calls.`support team` = 'it' AND calls.id = team_it.id
... etc

I didn't know what the join conditions are, so I guessed the calls.id = team_it.id

If the three tables already have a "foreign key" to calls, you can just left join on that instead.

Upvotes: 0

Related Questions