CODEWITHSUNDEEP
sqldatabase
user2930180
user2930180

Reputation: 3

Select rows where a value matches another value in another table

I have a question in regards to an SQL query I am trying to make that I am hoping someone can help me out with.

So I have three tables: activity, company and companygroup

I want to only select rows from the activity when the company is in group 1. But I also want to replace companyid with the company name in the final table.

Activity table:

+----+---------+----------+-----------+
| id | subject |   date   | comapnyid |
+----+---------+----------+-----------+
|  1 | Do this | 10-10-13 |     20985 |
|  2 | Do that | 11-11-13 |     18657 |
|  3 | Dont do | 12-12-13 |     22039 |
+----+---------+----------+-----------+

Company table:

+----+-----------+-------------+
| id | companyid | companyname |
+----+-----------+-------------+
|  1 |     20985 | Compone     |
|  2 |     18657 | Comptwo     |
|  3 |     22039 | Compthree   |
+----+-----------+-------------+

Companygroup table:

+----+-----------+---------+
| id | companyid | groupid |
+----+-----------+---------+
|  1 |     20985 |       1 |
|  2 |     20985 |       2 |
|  3 |     20985 |       3 |
|  4 |     18657 |       2 |
|  5 |     18657 |       3 |
|  6 |     22039 |       1 |
+----+-----------+---------+

I can get my output to give me a table with the company name next to all of the activities but I cannot figure out how to only select rows that the companyid matches up with group 1 in my companygroup table. I think I am having trouble because there are multiple group entries for each companyid, I am not sure how to cater for this.

Upvotes: 0

Views: 3348

Answers (2)

Esteban Elverdin
Esteban Elverdin

Reputation: 3582

try this:

SELECT a.id, a.subject, a.date, c.companyname
FROM Activity a 
INNER JOIN Company c ON a.companyid = c.companyid
INNER JOIN Companygroup cg ON c.companyid = cg.companyid
WHERE cg.groupid = 1

Upvotes: 0

Bhaskar Shrestha
Bhaskar Shrestha

Reputation: 342

select a.id, a.subject, a.date, b.companyname
from activity a inner join company b on a.companyid=b.companyid
    inner join companygroup c on b.companyid=c.companyid
where c.groupid=1

Upvotes: 1

Related Questions