Help me to join to tables ?

Question

I have 02 tables.

table1

   id_user  id_group
   0        32
   0        31
   0        33
   62       32
   62       31
   62       33
   120      31
   120      33


table2

   id    parent_id    name                 

   31    18            AAA                   
   32    18            BBB                  
   33    18            CCC

I want to get group of user from specific user (id_user )

for example user jame(id=62) will be output

AAA | BBB | CCC

for example user jame(id=120) will be output

AAA | None | CCC

Could anybody help me To make SQL Query to get user belong to groups? thanks!

Answer 1

You will need an outer join from your groups to the users like this:

SELECT *
FROM table2 LEFT JOIN table1 on table2.id = table1.id_group
WHERE table1.id_user = <some id>.

For each record in table2 (your groups) there will be a record in the result set. If the user is in this group, the table1-columns will be set, otherwise they will be NULLs.

In order to achieve your output of 'None' for which the user is not in the group, you'll have to do some function, for example

SELECT CASE WHEN table1.id is null THEN 'None' ELSE table2.name END
<and the rest of the code above>

The more natural thing in SQL would be to just output the names of the groups the user belongs to with a "normal" (an inner) join:

SELECT *
FROM table2 JOIN table1 on table2.id = table1.id_group
WHERE table1.id_user = <some id>.

For id=120 you'd get AAA and CCC. The fact that there is no record for BBB corresponds to the fact that the user is not a member of BBB.

Answer 2

As mentioned above, not sure if you require this in rows/columns or single field?

But you can try this

DECLARE @Table1 TABLE(
     id_user INT,
     id_group INT
)

INSERT INTO @Table1 SELECT 0, 32
INSERT INTO @Table1 SELECT 0, 31
INSERT INTO @Table1 SELECT 0, 33
INSERT INTO @Table1 SELECT 62, 32
INSERT INTO @Table1 SELECT 62, 31
INSERT INTO @Table1 SELECT 62, 33
INSERT INTO @Table1 SELECT 120, 31
INSERT INTO @Table1 SELECT 120, 33

DECLARE @Table2 TABLE(
     id INT,
     parent_id INT,
     NAME VARCHAR(10)
)

INSERT INTO @Table2 SELECT 31, 18, 'AAA'
INSERT INTO @Table2 SELECT 32, 18, 'BBB'
INSERT INTO @Table2 SELECT 33, 18, 'CCC'

DECLARE @user_id INT

SELECT @user_id = 120

DECLARE @RetVal VARCHAR(MAX)

SELECT  @RetVal = COALESCE(@RetVal + ' | ', '') + CASE WHEN sub.[NAME] IS NULL THEN 'None' ELSE sub.[NAME] END
FROM    @Table2 t2 LEFT JOIN
     (
      SELECT *
      FROM @Table1 t1 LEFT JOIN
        @Table2 t2 ON t2.id = t1.id_group
      WHERE t1.id_user = @user_id
     ) sub ON t2.id = sub.id

SELECT @RetVal