I have two tables and an in-between like
| project |
|---|
| id |
| group |
|---|
| id |
| project_group |
|---|
| project |
| group |
How can I get all projects by a groupId, where an entry (projectId, groupId) exists in project_group?
Asked
Active
Viewed 45 times
-2
Kazi Mohammad Ali Nur Romel
- 15,301
- 2
- 13
- 26
theonlygusti
- 11,032
- 11
- 64
- 119
1 Answers
-1
Since only Project_group has information for both project and group it's the only way to relate those two tables. In this scenario we need to join project with group so that we can find a project associated to a particular group.
Let's assume that we need to find all the project associated with group 1. The to select all the project - group relationship for group 1 we can write:
select project, group from project_group where group=1
This will return only the relationship but we need all the project related information such as code, name, cost etc.
On the other hand id from project table is same as project from project_group table. So we can join both the table with inner join on those columns as below:
select * from project p inner join project_group pg on p.id=pg.group
Now we will combine both the queries:
select * from project p inner join project_group pg on p.id=pg.group
and pg.group=1
It will return all the columns from project and project_group table. But to have all the columns from only project table you can use:
select p.* from project p inner join project_group pg on p.id=pg.group
and pg.group=1
Kazi Mohammad Ali Nur Romel
- 15,301
- 2
- 13
- 26
-
-
any name which is not a reserved word like pgroup. – Kazi Mohammad Ali Nur Romel Mar 09 '21 at 10:55
-
I don't think your answer will work, the last inner join confuses me. I know in advance what group I want to filter by – theonlygusti Mar 09 '21 at 10:57
-
-
your second answer will just return project ids and not projects themselves I think – theonlygusti Mar 09 '21 at 11:01
-
but I looked at the flagged duplicate and suspect this will work `SELECT * FROM project p, project_group pg WHERE pg.project = p.id AND pg.group = ?` – theonlygusti Mar 09 '21 at 11:02
-
1it's same as my changed first answer. if you only want project information then use p.* – Kazi Mohammad Ali Nur Romel Mar 09 '21 at 11:03
-
-
most welcome. Though I would suggest to use column names seperately like p.id, p.name etc. – Kazi Mohammad Ali Nur Romel Mar 09 '21 at 11:10
-
Please add some explanation to your answer such that others can learn from it – Nico Haase Mar 09 '21 at 15:15
-
@NicoHaase Thanks for the suggestion. I have tried. – Kazi Mohammad Ali Nur Romel Mar 09 '21 at 15:45
`group`and it's fine – theonlygusti Mar 09 '21 at 10:52