| Author |
 Topic  |
|
|
borthyn
Starting Member
4 Posts |
 Posted - 07/13/2012 : 06:08:58
|
Hi,
I wonder if anyone can help me with this problem. I am not quite sure how to phrase the question so excuse me if this a bit long winded.
I have 3 tables, User, Group and Department and a user can belong to multiple groups and to multiple departments.
Users Group Department
ID ID ID
User_Name Group_Name Dept_Name
User_ID User ID
Within these tables I have the following data.
Users
ID 1
User_Name John Smith
Group
ID 1 2 3
Group_Name Group1 Group2 Group3
User_ID 1 1 1
Department
ID 1 2 3
Dept_Name Dept1 Dept2 Dept3
User ID 1 1 1
I have created the following view to bring back all of the users, the Groups that they belong to and the Departments that they belong to.
SELECT U.User_Name, G.Group_Name, D.Dept_Name
FROM Users As U
INNER JOIN Group As G ON
U.ID = G.User_ID
INNER JOIN Department As D ON
U.ID = D.User_ID
This returns this set of data
User_Name Group_Name Dept_Name
John Smith Group1 Dept1
John Smith Group2 Dept1
John Smith Group3 Dept1
John Smith Group1 Dept2
John Smith Group2 Dept2
John Smith Group3 Dept2
John Smith Group1 Dept3
John Smith Group2 Dept3
John Smith Group3 Dept3
Is it possible to write a view on the above tables (I am not the dba and would not be allowed to amend the table structure in anyway) so that it would bring back the following data set?
User_Name Group_Name Dept_Name
John Smith Group1 Dept1
John Smith Group2 Dept2
John Smith Group3 Dept3
Thanks,
Borthyn |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/13/2012 : 10:04:40
|
SELECT User_Name,Group_Name,Dept_Name
FROM Users u
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY (SELECT 1) AS Seq,*
FROM Group)g
ON g.User_ID = u.User_ID
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY (SELECT 1) AS Seq,*
FROM Department)d
ON d.User_ID = u.User_ID
AND d.Seq = g.Seq
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
borthyn
Starting Member
4 Posts |
Posted - 07/17/2012 : 09:49:37
|
Thank you very much for taking the time to respond but I am getting the following error.
I am doing the SQL in Microsoft SQL Server 2005.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AS'.
Cheers
Andy |
|
|
|
jleitao
Yak Posting Veteran
Portugal
52 Posts |
Posted - 07/17/2012 : 09:55:01
|
SELECT User_Name,Group_Name,Dept_Name
FROM Users u
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY (SELECT 1)) AS Seq,*
FROM Group)g
ON g.User_ID = u.User_ID
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY (SELECT 1)) AS Seq,*
FROM Department)d
ON d.User_ID = u.User_ID
AND d.Seq = g.Seq
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/17/2012 : 10:00:45
|
quote:
Originally posted by borthyn
Thank you very much for taking the time to respond but I am getting the following error.
I am doing the SQL in Microsoft SQL Server 2005.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AS'.
Cheers
Andy
missed braces
SELECT User_Name,Group_Name,Dept_Name
FROM Users u
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY (SELECT 1)) AS Seq,*
FROM Group)g
ON g.User_ID = u.User_ID
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY (SELECT 1)) AS Seq,*
FROM Department)d
ON d.User_ID = u.User_ID
AND d.Seq = g.Seq
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
borthyn
Starting Member
4 Posts |
Posted - 07/17/2012 : 10:05:32
|
quote:
Originally posted by jleitao
SELECT User_Name,Group_Name,Dept_Name
FROM Users u
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY (SELECT 1)) AS Seq,*
FROM Group)g
ON g.User_ID = u.User_ID
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY (SELECT 1)) AS Seq,*
FROM Department)d
ON d.User_ID = u.User_ID
AND d.Seq = g.Seq
Wow - that is brilliant. Thanks you very much indead. I really do appreciate you taking the time to help.
Regards,
Andy
|
|
|
|
borthyn
Starting Member
4 Posts |
Posted - 07/18/2012 : 05:48:28
|
Just a quick note to say that I have implemented the SQL that you supplied me into a report today and my boss is very very pleased with the results.
Whist allowing some of the credit to reflect onto me, I did tell him that two selfless people had helped me out on this forum.
So visakh16 and jleitao thank you once again.
Andy |
Edited by - borthyn on 07/18/2012 05:49:38 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/18/2012 : 09:46:49
|
quote:
Originally posted by borthyn
Just a quick note to say that I have implemented the SQL that you supplied me into a report today and my boss is very very pleased with the results.
Whist allowing some of the credit to reflect onto me, I did tell him that two selfless people had helped me out on this forum.
So visakh16 and jleitao thank you once again.
Andy
no problem...you're welcome 
glad that we could help you out
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Single table to multiple table view
