Re: Filtering a voter?
From: Jon (Jon_at_discussions.microsoft.com)
Date: 12/27/04
- Next message: Chris White: "Re: DATEADD strange results"
- Previous message: Wendy Elizabeth: "display all data in a long text column"
- In reply to: John Bell: "Re: Filtering a voter?"
- Next in thread: John Bell: "Re: Filtering a voter?"
- Reply: John Bell: "Re: Filtering a voter?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 27 Dec 2004 12:13:06 -0800
Hi John,
Oh dear you're going to hate me but it did not work? It says that it is not
possible to parse query text? Here's the table script:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblPictureRating]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblPictureRating]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblPictures]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPictures]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblUsers]
GO
CREATE TABLE [dbo].[tblPictureRating] (
[VoteID] [int] IDENTITY (1, 1) NOT NULL ,
[PictureID] [int] NULL ,
[UserID] [int] NULL ,
[UserName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Rating] [decimal](4, 2) NULL ,
[Comments] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[DateOfVote] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblPictures] (
[PictureID] [int] IDENTITY (1, 1) NOT NULL ,
[OwnerID] [int] NULL ,
[OwnerUsername] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[PictureName] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[Description] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[Description2] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[Description3] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[A4size] [decimal](18, 2) NULL ,
[Poster] [decimal](18, 2) NULL ,
[ImgThumb] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[ImgMain] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[ImgSlice] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[CounterID] [int] NULL ,
[Promo] [bit] NULL ,
[PictureToneID] [int] NULL ,
[PictureTypeID] [int] NULL ,
[Rating] [int] NULL ,
[TotalVotes] [int] NULL ,
[RatingTotal] [int] NULL ,
[DatePictureAdded] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblUsers] (
[OwnerID] [int] IDENTITY (1, 1) NOT NULL ,
[OwnerUsername] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[OwnerPassword] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[UserFirst] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[UserLast] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Biography] [nvarchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
[dob] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Equipment] [nvarchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
[photoID] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[emailID] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Approved] [bit] NULL ,
[Image1] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Image3] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Image4] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Datejoined] [datetime] NULL ,
[SurveyID] [int] NULL ,
[lastHere] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPictures] WITH NOCHECK ADD
CONSTRAINT [PK_tblPictures] PRIMARY KEY CLUSTERED
(
[PictureID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblUsers] WITH NOCHECK ADD
CONSTRAINT [PK_tblUsers] PRIMARY KEY CLUSTERED
(
[OwnerID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPictureRating] WITH NOCHECK ADD
CONSTRAINT [DF_tblPictureRating_Comments] DEFAULT (N'<NULL>') FOR
[Comments],
CONSTRAINT [DF_tblPictureRating_Date] DEFAULT (getdate()) FOR [DateOfVote]
GO
ALTER TABLE [dbo].[tblPictures] WITH NOCHECK ADD
CONSTRAINT [DF_tblPictures_Rating] DEFAULT (0) FOR [Rating],
CONSTRAINT [DF_tblPictures_TotalVotes] DEFAULT (0) FOR [TotalVotes],
CONSTRAINT [DF_tblPictures_RatingTotal] DEFAULT (0) FOR [RatingTotal],
CONSTRAINT [DF_tblPictures_DatePictureAdded] DEFAULT (getdate()) FOR
[DatePictureAdded]
GO
ALTER TABLE [dbo].[tblUsers] WITH NOCHECK ADD
CONSTRAINT [DF_tblUsers_Biography] DEFAULT (N'...') FOR [Biography],
CONSTRAINT [DF_tblUsers_Equipment] DEFAULT (N'...') FOR [Equipment],
CONSTRAINT [DF_tblUsers_Datejoined] DEFAULT (getdate()) FOR [Datejoined]
GO
"John Bell" wrote:
> Hi
>
> There would be no point in listing the Rating username then! It is not clear
> why you have tblUsers with OwnerUsername, I would guess this is in
> tblPictures.
>
> SELECT P.PictureID, U.OwnerUsername, P.PictureName
> FROM dbo.tblUsers U
> JOIN dbo.tblPictures P ON U.OwnerID = P.OwnerID
> WHERE NOT EXISTS ( SELECT * FROM
> dbo.tblPictureRating R
> WHERE P.PictureID = R.PictureID
> AND U.OwnerUsername = R.UserName )
> WHERE U.UserName = N'jonpanky'
> AND U.OwnerUsername <> N'jonpanky'
> GROUP BY U.OwnerUsername, P.PictureName, P.PictureID
> ORDER BY P.PictureID
>
> John
>
> "Jon" <Jon@discussions.microsoft.com> wrote in message
> news:453035B9-589C-4EB5-949D-0FD5369D566C@microsoft.com...
> > Thanks, i'll read the etiquette...
> >
> > But it's easy to find a photograh that they have voted on, What I want
> > to
> > do is create a list that he has not voted on?
> >
> > Many thanks
> > Jon
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> Posting DDL and example data always helps when asking questions like
> >> these
> >> http://www.aspfaq.com/etiquette.asp?id=5006
> >>
> >> At a guess something like the following will produce a list that
> >> 'jonpanky'
> >> has voted on:
> >> SELECT P.PictureID, U.OwnerUsername, P.PictureName, R.UserName
> >> FROM dbo.tblUsers U
> >> JOIN dbo.tblPictures P ON U.OwnerID = P.OwnerID
> >> JOIN dbo.tblPictureRating R ON P.PictureID = R.PictureID
> >> AND U.OwnerUsername <> R.UserName
> >> WHERE R.UserName = N'jonpanky'
> >> GROUP BY U.OwnerUsername, P.PictureName, P.PictureID, R.UserName
> >> ORDER BY P.PictureID
> >>
> >> Although you should not allow them to vote on their own pictures
> >> therefore
> >> AND U.OwnerUsername <> R.UserName
> >> should be redundant.
> >>
> >> John
> >>
> >> "Jon" <Jon@discussions.microsoft.com> wrote in message
> >> news:63FE06CE-B3D3-49E0-A73B-002F7E47CD0E@microsoft.com...
> >> > Hello,
> >> >
> >> > I have three Tables tblUsers tblPictures and tblPictureRating
> >> >
> >> > On my site the photographers can rate other photographers work, what
> >> > I'm
> >> > trying to to do is filter out the photographs that a user may have
> >> > voted
> >> > on,
> >> > but for the life of me I'm struggling can you help here is the SQL that
> >> > I'm
> >> > using:
> >> >
> >> > SELECT dbo.tblPictures.PictureID, dbo.tblUsers.OwnerUsername,
> >> > dbo.tblPictures.PictureName, dbo.tblPictureRating.UserName
> >> >
> >> > FROM dbo.tblUsers INNER JOIN
> >> >
> >> > dbo.tblPictures ON dbo.tblUsers.OwnerID =
> >> > dbo.tblPictures.OwnerID INNER JOIN
> >> >
> >> > dbo.tblPictureRating ON dbo.tblPictures.PictureID
> >> > =
> >> > dbo.tblPictureRating.PictureID
> >> >
> >> > WHERE (dbo.tblPictureRating.UserName = N'jonpanky')
> >> >
> >> > GROUP BY dbo.tblUsers.OwnerUsername, dbo.tblPictures.PictureName,
> >> > dbo.tblPictures.PictureID, dbo.tblPictureRating.UserName
> >> >
> >> > HAVING (dbo.tblUsers.OwnerUsername <> N'jonpanky')
> >> >
> >> > ORDER BY dbo.tblPictures.PictureID
> >> >
> >> > Thanks if you can help me.
> >> >
> >> > Regards
> >> > Jon
> >>
> >>
> >>
>
>
>
- Next message: Chris White: "Re: DATEADD strange results"
- Previous message: Wendy Elizabeth: "display all data in a long text column"
- In reply to: John Bell: "Re: Filtering a voter?"
- Next in thread: John Bell: "Re: Filtering a voter?"
- Reply: John Bell: "Re: Filtering a voter?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
