Help - Union Select - Using VB.Net

[bunary2k]

i have 2 tables :
1.Ms_Menu
Menu_Id,Menu_Name
001 | Menu1
002 | Menu2
003 | Menu3

2.Tbl_User
User_Id,User_Name,Menu_Id
User1 |Name1 |001
User1 |Name2 |002
User2 |Name2 |002

i want to joined 2 tables using UNION(or other way if works is fine->left/right join)
my coding :

SELECT User_Id,User_Name,Menu_Id,'' Menu_Name
FROM dbo.Tbl_User
UNION ALL SELECT '' User_Id,'' User_Name,Menu_Id,Menu_Name
FROM dbo.Ms_Menu

THE RESULT :

User_Id,User_Name,Menu_Id,Menu_Name
User1 |Name1 |001 |Menu1
User1 |Name1 |002 |Menu2
User2 |Name2 |001 |Menu1
| |001 |Menu1
| |002 |Menu2
| |003 |Menu3

ALL I WANT THE RESULT IS :

User1 |Name1 |001 |Menu1
User1 |Name1 |002 |Menu2
| |003 |Menu3

OR

User2 |Name2 |001 |Menu1
| |002 |Menu2
| |003 |Menu3

all depend on User_Id i want to query

tq
Pa'UL

[edmicman]

Hmmmm, I think you want a join instead. Try something like this:

SELECT User_Id,User_Name,Ms_Menu.Menu_Id,Menu_Name
FROM dbo.Ms_Menu LEFT OUTER JOIN dbo.Tbl_User ON Ms_Menu.Menu_Id = Tbl_User.Menu_Id

I think that gets you close, although you might need to tweak it a bit.

[debasisdas]

when you are posting such a questiln always post what you have tried so far to solve that . That will give us an idea on your exact requirment.