Aggregating results of two database views in one database view

Question

I have a view with name of UserInfo like this:

And another view with name of UserRoleInfo like this:

I want to aggregate all the records of the two views for one user name like H.F into one view.

I tried to join the two views on user name but does not work!

How can I have all records of these two views in one view with the same columns as in picture 3?

Answer 1

You want to use the UNION ALL operator, rather than trying to JOIN on Username.

A UNION ALL returns a combination of all results from Query A and Query B.

Alternatively, if you only wanted DISTINCT records from the set, you could omit the ALL keyword and just do a UNION.

SELECT
    UserID,
    Username,
    OperationFaName,
    OperationEnName,
    ComponentName,
    AccessString,
    ApplicationName,
    ApplicationID
FROM UserInfo

UNION ALL

SELECT
    UserID,
    Username,
    OperationFaName,
    OperationEnName,
    ComponentName,
    AccessString,
    ApplicationName,
    ApplicationID
FROM UserRoleInfo

Answer 2

A JOIN will create a view with all the columns from both views. This will give you duplicate columns and values for the parent records.

It sounds like you want to do a UNION without some of the columns in the second view.

Just take the Select that generates the 1st view type UNION under it and then copy that select and paste it under the UNION key word and change the FROM section to the 2nd view instead of the 1st view. This will select the same columns from both views and combine them as you have asked.