July 8, 2010 at 5:15 am
Hi Guys,
I have a query that joins two tables. Basically I have a meeting table with fields like MeetingID,Name,Description,DateCreated.
Then I have a table Called Invited_meeting_users which has fields
(ID, MeetingID, UserID).
Now in my current view I join the two tables and get all the fields from the meeting table and just the userid field from the Invited_meeting_users table.
Then on my front end app I need to show the logged in user the meetings he is invited to.
So i select from my view where userid = current logged in userid. Easy enough.
Now my problem is. Data in view looks like this:
3Test 1sdfewfNS303
3Test 1sdfewfNS472
3Test 1sdfewfNS599
8Test 2303
8Test 2599
9Test 2NULL
Foe meetingid 3 user 599 is invited. for meetingid 8 he is invited as well.
I need to show all meetings to a specific group of people. So I cant just select all records from my view as the meetings are repeated(meeting id 3 and 8 appear multiple times as they have multiple users)
I can't group by in my view as it won't allow me to group by just meetingid fro example. I have to group by all the fields in the select part of my query. So when I add userid into my query all records still appear. I need it to look like this for the group of users that see all meetings:
3Test 1sdfewfNS
8Test 2
9Test 2
just the 3 meetings. no duplicates. and userid is not needed.
But in my 1st part as explained above I need userid. How do I get past this. please help
July 8, 2010 at 6:07 am
niter can you show us the current query you are using? we can probably infer a lot from that, but the CREATE VIEW definition would help as well;
the requrement looks pretty easy, but it'll waste everyones time if i guess at a schema, post an answer based on my version of your schema, and then we follow up with multiple posts fora adjustments because my guess doesn't match your actual schema.
Lowell
July 8, 2010 at 6:24 am
Hi Lowell,
this is my view
GO
/****** Object: View [dbo].[OSC_Users_Meetings] Script Date: 07/08/2010 14:15:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[OSC_Users_Meetings]
AS
SELECT DISTINCT m.MeetingID, m.Name, m.Description, m.Venue, mm.UserID
FROM dbo.OSC_Meeting AS m LEFT OUTER JOIN
dbo.OSC_Invited_Meeting_Members AS mm ON m.MeetingID = mm.MeetingID AND mm.Active = 1
WHERE (m.Active = 1)
my meeting table
GO
/****** Object: Table [dbo].[OSC_Meeting] Script Date: 07/08/2010 14:16:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OSC_Meeting](
[MeetingID] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [varchar](150) NULL,
[Description] [varchar](200) NULL,
[Active] [bit] NULL CONSTRAINT [DF_OSC_Meeting_Active] DEFAULT ((1)),
[Venue] [varchar](150) NULL,
[DateCreated] [datetime] NULL,
CONSTRAINT [PK_OSC_Meeting] PRIMARY KEY CLUSTERED
(
[MeetingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
my meeting_users table:
GO
/****** Object: Table [dbo].[OSC_Invited_Meeting_Members] Script Date: 07/08/2010 14:17:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OSC_Invited_Meeting_Members](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[MeetingID] [bigint] NOT NULL,
[UserID] [int] NOT NULL,
[Active] [bit] NOT NULL CONSTRAINT [DF_OSC_Invited_Meeting_Members_Active] DEFAULT ((1)),
CONSTRAINT [PK_OSC_Meeting_Members] 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]
GO
ALTER TABLE [dbo].[OSC_Invited_Meeting_Members] WITH CHECK ADD CONSTRAINT [FK_OSC_Meeting_Members_OSC_Meeting] FOREIGN KEY([MeetingID])
REFERENCES [dbo].[OSC_Meeting] ([MeetingID])
GO
ALTER TABLE [dbo].[OSC_Invited_Meeting_Members] CHECK CONSTRAINT [FK_OSC_Meeting_Members_OSC_Meeting]
test data:
Meeting Table:
MeetingID Name Description dateCreated
3 Test 1 Test 1
8 Test 2 Test 2
Meeting Users:
ID MeetingID UserID
1 3 599
2 3 100
3 8 599
Now with my current view I can select all meeting data with userid. And in my front end I filter by userid.
But say I want to show all meetings and not filter by userid. I would now get all meetings if I run my view, but 2 instances of meeting 3 as there are 2 users in that meeting.
I need to show each meeting once but I have no idea how to achieve both these scenarios with one view
July 8, 2010 at 8:52 am
Can we get you to post your sample data in the form of insert statements? This will make it A LOT easier to test the result before we post it. (See the first link in my signature for how to do this.)
Thanks!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply