Select a column from multiple tables and return as one table

Question

I have a simple module base settings tables in database as below :

Module1
UserId                                 | Active      // other columns
*00000000-0000-0000-0000-000000000AB1* | *True*

Module2
UserId                                 | Active     // other columns
*00000000-0000-0000-0000-000000000AB1* | *False*

Module3
UserId                                 | Active     // other columns
*00000000-0000-0000-0000-000000000AB1* | *True*

I want to select if modules are active or not for specific user and output result something like this

Module1 | Module2 | Module3
True    | False   | True

I have tried this query using Join :

SELECT Module1 = module1.Active, Module2 = module2.Active, Module3 = module3.Active
FROM [dbo].[Module1] AS module1 JOIN
[dbo].[Module2] AS module2 ON module1.UserId = module2.UserId JOIN
[dbo].[Module3] AS module3 ON module2.UserId = module3.UserId JOIN
WHERE module1.UserId = '00000000-0000-0000-0000-000000000AB1'

that gives me the exact output what I required. But if there is not a matching row for that user in any of the table, then all columns will be empty in result set.

How should I update my query to handle if any table has not a matching record so that value for that table's column be null or default value in result set.

Edit : datatype of UserId is uniqueidentifier and for Active is bit.

Use `FULL OUTER JOIN` instead of join. or use a [Pivot](http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) functionality instead of multiple join and union tables together. — xQbert, Sep 17 '15 at 13:31
@xQbert, thank you! I need to select two columns from these modules, so need to create matrix like result for modules, using Pivot as you suggested may be. But I have to ask separate question for that I think. — Mukesh Modhvadiya, Sep 17 '15 at 13:52
@KrishnrajRana, pardon me but I have never used that tool, but I am updating the question than. — Mukesh Modhvadiya, Sep 17 '15 at 14:03

Hogan · Accepted Answer · 2015-09-18T11:28:45.973

1

If you have (or make with a CTE as I did) a table with all the IDs you are looking for, then you just need to left join from that table:

WITH searchfor AS
(
    SELECT '00000000-0000-0000-0000-000000000AB1' as UserID
)
SELECT s.UserID, 
       COALESCE(m1.Active,FALSE) as Module1,
       COALESCE(m2.Active,FALSE) as Module2,
       COALESCE(m3.Active,FALSE) as Module3
FROM searchfor s
LEFT JOIN Module1 m1 on s.UserID = m1.Userid
LEFT JOIN Module2 m2 on s.UserID = m2.Userid
LEFT JOIN Module3 m3 on s.UserID = m3.Userid

Note, if Active is a string then change it to COALESCE(mX.Active,'FALSE')

Saw edit, try COALESCE(mX.Active,(CAST 0 as BIT))

If you want all users do this

WITH searchfor AS
(
    SELECT UserID from Module1
    UNION
    SELECT UserID from Module2
    UNION
    SELECT UserID from Module3
)

edited Sep 18 '15 at 11:28

answered Sep 17 '15 at 14:16

Hogan

69,564
10
76
117

Thank you for the reply, but it returns FALSE for all modules if m1.Active is NULL or FALSE. Returns TRUE for all if m1.Active is TRUE. – Mukesh Modhvadiya Sep 18 '15 at 11:09
@mak yes I had a typo, I fixed it – Hogan Sep 18 '15 at 11:29
Oh even I failed to notice that as I was trying too many things. But it's working perfectly. Thanks a lot!! – Mukesh Modhvadiya Sep 18 '15 at 12:20

Stanislovas Kalašnikovas · Answer 2 · 2015-09-17T13:54:55.933

0

You can use FULL JOIN instead of INNER JOIN. I don't know really about your WHERE clause, but you can do something like this:

SELECT Module1 = module1.Active, Module2 = module2.Active, Module3 = module3.Active
FROM [dbo].[Module1] AS module1 
FULL JOIN [dbo].[Module2] AS module2 ON module1.UserId = module2.UserId 
FULL JOIN [dbo].[Module3] AS module3 ON module2.UserId = module3.UserId
WHERE (module1.UserId = '00000000-0000-0000-0000-000000000AB1' OR module1.UserId IS NULL) OR 
      (module2.UserId = '00000000-0000-0000-0000-000000000AB1' OR module2.UserId IS NULL) OR 
      (module3.UserId = '00000000-0000-0000-0000-000000000AB1' OR module3.UserId IS NULL)

UPDATE

Or you can use CASE in following:

SELECT Module1 = module1.Active, Module2 = module2.Active, Module3 = module3.Active
FROM [dbo].[Module1] AS module1 
FULL JOIN [dbo].[Module2] AS module2 ON module1.UserId = module2.UserId 
FULL JOIN [dbo].[Module3] AS module3 ON module2.UserId = module3.UserId
WHERE CASE WHEN module1.UserId IS NULL THEN module2.UserId
           WHEN module2.UserId IS NULL THEN module3.UserId
      END = '00000000-0000-0000-0000-000000000AB1'

edited Sep 17 '15 at 13:54

answered Sep 17 '15 at 13:31

Stanislovas Kalašnikovas

9,203
4
32
59

Left join only works if users had records in an earlier join. Need to use a full outer. if user is not in module 1 but in module 3 this wouldn't return correct results. – xQbert Sep 17 '15 at 13:32
Still this will bring only records when `module1.UserId = '00000000-0000-0000-0000-000000000AB1'`. Not all the records – Pரதீப் Sep 17 '15 at 13:34
thank you for the answer, but it is returning me multiple rows because of OR conditions may be. – Mukesh Modhvadiya Sep 17 '15 at 13:48
@Mak so provide widely sample data with NULL samples and desired output. Also check updated answer. – Stanislovas Kalašnikovas Sep 17 '15 at 13:50
@StanislovasKalašnikovas, I am selecting values for one user, and in each table there would be only one row for that user. So result set should contain only one row. With null column values for which table has not matching raw available. If this detail is not enough please let me know, I will update the question. – Mukesh Modhvadiya Sep 17 '15 at 13:57

score 0 · Answer 3 · answered Sep 17 '15 at 14:02

BOTH UNTESTED:

Building on what Stanislovas did...

This may work...

SELECT module1.Active as Module1, module2.Active as Module2, module3.Active as Module3
FROM [dbo].[Module1] AS module1 
FULL OUTER JOIN [dbo].[Module2] AS module2 ON module1.UserId = module2.UserId 
  and (module1.UserId = '00000000-0000-0000-0000-000000000AB1' or    
       module2.UserId = '00000000-0000-0000-0000-000000000AB1')
FULL OUTER JOIN [dbo].[Module3] AS module3 ON module2.UserId = module3.UserId
  and (module2.UserId = '00000000-0000-0000-0000-000000000AB1' OR 
       module3.UserId = '00000000-0000-0000-0000-000000000AB1')
GROUP BY  Module1, Module2, Module3, module1.userID

Though a pivot with union results seems like it would work as well...

SELECT * FROM 
(
  select Active
  from (
  Select M1.Active, userID, 'Module1' as Module from module1 m1
  UNION ALL
  Select M2.Active, userID, 'Module2' from module2 m2
  Union all
  Select M3.Active, userID, 'Module3' from module3 m3) iQry
  WHERE  userID = '00000000-0000-0000-0000-000000000AB1'
pivot
(
  for module in ('Module1','Module2','Module3')
) piv;

@Qbert, Thank you for the reply, can you please check 2nd query, because it shows syntactical errors near `iQry` and `for module`, I have not used pivot functionality ever. — Mukesh Modhvadiya, Sep 18 '15 at 11:21

score 0 · Answer 4 · answered Sep 17 '15 at 14:14

This works but first joining table i.e. here Module1 must not be null

SELECT  Module1 = t1.[ACTIVE]
        , Module2 = t2.Active
        , Module3 = t3.Active 
FROM Module1 t1
    FULL JOIN Module2 t2 ON t1.userid = t2.userid
    FULL JOIN Module3 t3 ON t1.userid = t3.userid
WHERE t1.userid = '00000000-0000-0000-0000-000000000AB1'

Select a column from multiple tables and return as one table

4 Answers4