March 29, 2012 at 8:21 am
I have two tables :
Users
ID | Name
1 | Bob
2 | Robert
and
BackOffice
ID | UserId | BackOfficeID
1 | 1 | 711
2 | 1 | 711
The query to modified looks like :
SELECT Users.Id, Users.Name, BackOffice.BackOfficeID FROM Users INNER JOIN BackOffice ON Users.ID = BackOffice.UserID
When I do an INNER JOIN with the condition WHERE BackOfficeID = 711
(I cannot change the field in the condition, it has to be BackOfficeID)
I have two times the user "Bob", I need a TOP 1 or something to be able to get only one line.
Is it possible?
Thank you
March 29, 2012 at 8:29 am
Two times the user Bob? Surely you'll get a Bob and a Robert? Now, how are you going to decide which one of those to choose if you only want TOP 1?
John
March 29, 2012 at 8:34 am
John is correct in what he says. Your query will return Bob and Robert if you add that where clause.
What you are trying to code from what you have said is
"Bob and Robert both sit in the sales office. I want to know the TOP 1 person who sits in the Sales office"
As John mentions, it makes no sense. You either want to know everyone who works in that office or just that someone does work in the office.
What is your desired result?
March 29, 2012 at 8:36 am
You'll want to use an APPLY. Since you're data doesn't match your current results, this is a pure guess at what you want.
SELECT u.[ID], bo.BackOfficeID
FROM Users AS u
CROSS APPLY (
SELECT TOP(1) bo.BackOfficeID
FROM BackOffice AS bo
WHERE bo.[ID] = u.[ID]
AND bo.BackOfficeID = 711
-- you will need to add an order by
) AS bo
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 29, 2012 at 8:39 am
My bad, wrote the post too quickly, I edited the original post, I was missing the UserId and corrected the INNER JOIN clause.
March 29, 2012 at 8:45 am
The question still stands. You are asking for everyone who is in office 711, which both Bob and Robert are.
What are you trying to achieve? Do you want one result line with all the names in that office?
March 29, 2012 at 8:51 am
Animal Magic (3/29/2012)
The question still stands. You are asking for everyone who is in office 711, which both Bob and Robert are.What are you trying to achieve? Do you want one result line with all the names in that office?
No, he's joining on BackOffice.userid. Looks like there should be a unique constraint on UserID and BackOfficeID, though. I think we could do with some table DDL and sample data in the form of INSERT statements (more than just those two lines), please.
John
March 29, 2012 at 9:02 am
Here's the sample code :
CREATE TABLE [dbo].[Users](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](20) NULL,
[Email] [varchar](20) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET IDENTITY_INSERT [dbo].[Users] ON
INSERT [dbo].[Users] ([ID], [UserName], [Email]) VALUES (1, N'Bob', N'bob@bob.com')
INSERT [dbo].[Users] ([ID], [UserName], [Email]) VALUES (2, N'Robert', N'robert@bob.com')
INSERT [dbo].[Users] ([ID], [UserName], [Email]) VALUES (3, N'Bill', N'bill@bob.com')
SET IDENTITY_INSERT [dbo].[Users] OFF
CREATE TABLE [dbo].[BackOffice](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserId] [int] NOT NULL,
[BackOfficeId] [int] NOT NULL,
CONSTRAINT [PK_BackOffice] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET IDENTITY_INSERT [dbo].[BackOffice] ON
INSERT [dbo].[BackOffice] ([ID], [UserId], [BackOfficeId]) VALUES (1, 1, 711)
INSERT [dbo].[BackOffice] ([ID], [UserId], [BackOfficeId]) VALUES (2, 1, 711)
INSERT [dbo].[BackOffice] ([ID], [UserId], [BackOfficeId]) VALUES (3, 2, 712)
INSERT [dbo].[BackOffice] ([ID], [UserId], [BackOfficeId]) VALUES (4, 3, 713)
SET IDENTITY_INSERT [dbo].[BackOffice] OFF
SELECT BackOffice.Id, BackOffice.BackOfficeId, Users.ID, Users.UserName, Users.Email
FROM BackOffice INNER JOIN
Users ON BackOffice.UserId = Users.ID
Thanks
March 29, 2012 at 9:06 am
something like this will do the job if you dont need to return what appears to be a pseudo primary key on the backoffice table
SELECT BackOffice.BackOfficeId, Users.ID, Users.UserName, Users.Email
FROM Users INNER JOIN
(Select UserID, BackOfficeID from BackOffice group by UserID, BackOfficeID) backOffice ON BackOffice.UserId = Users.ID
March 29, 2012 at 9:12 am
OK so now what is you want to retrieve from this?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 29, 2012 at 9:13 am
Animal Magic is right. However, doing this is just a kludge. What you should do, if it is in your power, is add a unique constraint on BackOffice(UserID,BackOfficeID). That would stop the duplicates getting into your data in the first place.
John
March 29, 2012 at 9:18 am
Only one line of each user in the Users table.
March 29, 2012 at 9:22 am
TcW_1978 (3/29/2012)
Here's the sample code :
CREATE TABLE [dbo].[Users](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](20) NULL,
[Email] [varchar](20) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET IDENTITY_INSERT [dbo].[Users] ON
INSERT [dbo].[Users] ([ID], [UserName], [Email]) VALUES (1, N'Bob', N'bob@bob.com')
INSERT [dbo].[Users] ([ID], [UserName], [Email]) VALUES (2, N'Robert', N'robert@bob.com')
INSERT [dbo].[Users] ([ID], [UserName], [Email]) VALUES (3, N'Bill', N'bill@bob.com')
SET IDENTITY_INSERT [dbo].[Users] OFF
CREATE TABLE [dbo].[BackOffice](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserId] [int] NOT NULL,
[BackOfficeId] [int] NOT NULL,
CONSTRAINT [PK_BackOffice] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET IDENTITY_INSERT [dbo].[BackOffice] ON
INSERT [dbo].[BackOffice] ([ID], [UserId], [BackOfficeId]) VALUES (1, 1, 711)
INSERT [dbo].[BackOffice] ([ID], [UserId], [BackOfficeId]) VALUES (2, 1, 711)
INSERT [dbo].[BackOffice] ([ID], [UserId], [BackOfficeId]) VALUES (3, 2, 712)
INSERT [dbo].[BackOffice] ([ID], [UserId], [BackOfficeId]) VALUES (4, 3, 713)
SET IDENTITY_INSERT [dbo].[BackOffice] OFF
SELECT BackOffice.Id, BackOffice.BackOfficeId, Users.ID, Users.UserName, Users.Email
FROM BackOffice INNER JOIN
Users ON BackOffice.UserId = Users.ID
Thanks
Is it valid for a user to have duplicate records in dbo.BackOffice (with the exception of the ID field) as Bob does in this example?
March 29, 2012 at 9:29 am
Yes, that is part of the problem, unfortunately, we cannot change this and add a unique constraint.
March 29, 2012 at 9:44 am
Did you try the CROSS APPLY? The only change will be that you need to change the WHERE clause to bo.userid = u.[id]
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply