Sql query

[jonpanky]

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

 

[rac2]

what I'm trying to to do is filter out the photographs that a user may have voted on"

Suppose jonpanky is a user and he has voted on some pictures. Pictures he has not voted on are likely to be these.

SELECT     dbo.tblPictures.PictureID, 
FROM dbo.tblPictures
LEFT JOIN dbo.tblPictureRating ON
       dbo.tblPictures.PictureID = dbo.tblPictureRating.PictureID
       AND dbo.tblPictureRating.UserName = N'jonpanky'
WHERE dbo.tblPictureRating.PictureID IS NULL
ORDER BY dbo.tblPictures.PictureID

The LEFT JOIN insures that there will be a row in the results for every picture in tblPictures. Those with ratings by jonpanky could have data from tblPictureRating; the remainder will have NULL for any columns in the rating table, so the PictureID column is as good as any as a condition.


If you want to rule out pictures that are owned by jonpanky then add a condition for that.

SELECT     dbo.tblPictures.PictureID, 
FROM dbo.tblPictures
LEFT JOIN dbo.tblPictureRating ON
       dbo.tblPictures.PictureID = dbo.tblPictureRating.PictureID
       AND dbo.tblPictureRating.UserName = N'jonpanky'
WHERE dbo.tblPictureRating.PictureID IS NULL
  AND dbo.tblPictures.OwnerID <> jonpankyID
ORDER BY dbo.tblPictures.PictureID

Or if you have only the raters name, then JOIN tblUsers so that the name can be used as the condition.

SELECT     dbo.tblPictures.PictureID, 
FROM dbo.tblPictures
JOIN dbo.tblUsers ON
       dbo.tblUsers.OwnerID = dbo.tblPictures.OwnerID 
LEFT JOIN dbo.tblPictureRating ON
       dbo.tblPictures.PictureID = dbo.tblPictureRating.PictureID
       AND dbo.tblPictureRating.UserName = N'jonpanky'
WHERE dbo.tblPictureRating.PictureID IS NULL
  AND dbo.tblUsers.OwnerUsername <> N'jonpanky'
ORDER BY dbo.tblPictures.PictureID

 

[jonpanky]

Thank you Rac2 I'll try that tonight.

 

[jonpanky]

Hi thanks for all your help but it did not work, syntax error near where?

I think this should give you the table format:

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 ,

[nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,

[nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,

[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