Search code examples
sqlms-access

Finding Records in a table using result of query of another table in SQL

I have two tables

Groups:

id  group_name
--------------
1   G1
2   G2
3   G3

Permissions:

PersId  Groups_id
-----------------
10001   1
10001   2
10002   2
10003   3

This means 10001 can access to G1 and G2 and 10002 can access G2 and G3.

I want to write a query to find groups name that for example 10001 can access.

I have written a SQL query like this:

SELECT * 
FROM Permissions 
WHERE PersId.id = [p_id]

but this query returns just 1 and 2. I want something to return G1 and G2

Solution

  • You can join two tables to get the right result like this :

    SELECT Permissions.persid,group_name 
FROM       Permissions 
INNER JOIN Groups 
        ON Permissions.Groups_id = Groups.Id
WHERE Permissinos.PersId = 10001

    This query will return the following output:

    persid group_name
    10001 G1
    10001 G2