February 7, 2012 at 3:19 am
Im having trouble getting specific recordset...
I have two tables.
#user and #work
A user can have multiple worktitles, but i want to select only the first worktitle for each user.
I need this so i can have unique users in each row...
BEGIN TRAN
CREATE TABLE #user
(
u_id int,
name varchar (10)
)
insert into #user values (1,'steven')
insert into #user values (2,'david')
CREATE TABLE #work
(
w_id int,
userid int,
title varchar (10)
)
insert into #work values (1,1,'janitor')
insert into #work values (2,1,'teacher')
insert into #work values (3,2,'mechanic')
insert into #work values (4,2,'engineer')
select name,title from #user INNER JOIN #work ON #user.u_id=#work.userid
ROLLBACK
The result now:
stevenjanitor
steventeacher
david mechanic
david engineer
Desired result:
stevenjanitor
david mechanic
Ty for your time
February 7, 2012 at 3:29 am
Loads of ways to do this.
Here's two.
BEGIN TRAN
CREATE TABLE #user (u_id INT, NAME VARCHAR(10));
INSERT INTO #user VALUES (1, 'steven');
INSERT INTO #user VALUES (2, 'david');
CREATE TABLE #work (w_id INT, userid INT, title VARCHAR(10));
INSERT INTO #work VALUES (1, 1, 'janitor');
INSERT INTO #work VALUES (2, 1, 'teacher');
INSERT INTO #work VALUES (3, 2, 'mechanic');
INSERT INTO #work VALUES (4, 2, 'engineer');
--Method 1
SELECT NAME, title
FROM #user us
OUTER APPLY (SELECT TOP 1 u_id, w_id, title
FROM #user
INNER JOIN #work ON #user.u_id = #work.userid
WHERE us.u_id = u_id
ORDER BY w_id) tp;
--Method 1
SELECT NAME, title
FROM #user us
OUTER APPLY (SELECT TOP 1 title
FROM #work
WHERE us.u_id = userid
ORDER BY w_id) tp;
--Method 2
SELECT NAME, title
FROM (SELECT NAME, title, w_id, MIN(w_id) OVER(PARTITION BY u_id) AS uw_id
FROM #user
INNER JOIN #work ON #user.u_id = #work.userid) innerQ
WHERE uw_id = w_id
ROLLBACK
February 7, 2012 at 3:39 am
Cadavre (2/7/2012)
Loads of ways to do this.
Ah ty!
Lets see if i can apply it... have alot of inner join in the real query...
Conclusion: It worked with my query.. ty!
After some slight modification this worked out very well
SELECT NAME, title
FROM #user us
OUTER APPLY (SELECT TOP 1 title
FROM #work
WHERE us.u_id = userid
ORDER BY w_id) tp;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply