Select Query Where Results of 1 are not in 2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select Query Where Results of 1 are not in 2

Author

Topic

CreativeNRG
Starting Member

44 Posts

Posted - 2008-02-28 : 02:42:15

I have the following view:

SELECT TOP 100 PERCENT dbo.Tournaments.VenueId,
COUNT(dbo.Results.ResultId) AS Total,
dbo.Tournaments.LeagueWeekId,
dbo.LeagueWeeks.LeagueId
FROM dbo.Tournaments
INNER JOIN dbo.Results
ON dbo.Tournaments.TournamentId = dbo.Results.TournamentId
INNER JOIN dbo.Venues
ON dbo.Tournaments.VenueId = dbo.Venues.VenueId
INNER JOIN dbo.LeagueWeeks
ON dbo.Tournaments.LeagueWeekId = dbo.LeagueWeeks.LeagueWeekId
WHERE (dbo.Tournaments.Status = 2)
GROUP BY dbo.Tournaments.VenueId,dbo.Tournaments.LeagueWeekId,dbo.LeagueWeeks.LeagueId
ORDER BY dbo.Tournaments.VenueId,
dbo.Tournaments.LeagueWeekId

From this view I want to extract all venue ids from 1 league( dbo.LeagueWeeks.LeagueId) that are not in another leagaue( dbo.LeagueWeeks.LeagueId)

Any help would be much appreciated.

www.creativenrg.co.uk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-28 : 02:56:31

Can you also give table structures?

CreativeNRG
Starting Member

44 Posts

Posted - 2008-03-08 : 16:45:50

Sorry for delayed reply, still stuck on this:

CREATE TABLE [dbo].[LeagueWeeks] (
[LeagueWeekId] [int] IDENTITY (1, 1) NOT NULL ,
[LeagueId] [int] NOT NULL ,
[Week] [int] NOT NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Results] (
[ResultId] [int] IDENTITY (1, 1) NOT NULL ,
[TournamentId] [int] NOT NULL ,
[UserId] [int] NOT NULL ,
[Points] [int] NOT NULL ,
[Position] [int] NOT NULL ,
[Bonus1] [bit] NOT NULL ,
[Bonus2] [bit] NOT NULL ,
[Bonus3] [bit] NOT NULL ,
[Created] [smalldatetime] NOT NULL ,
[Updated] [smalldatetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Tournaments] (
[TournamentId] [int] IDENTITY (1001, 1) NOT NULL ,
[LeagueWeekId] [int] NOT NULL ,
[VenueId] [int] NOT NULL ,
[Date] [smalldatetime] NULL ,
[Status] [int] NOT NULL
) ON [PRIMARY]
GO

www.creativenrg.co.uk

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 17:36:25

Maybe something like this:


SELECT s1.VenueID
	  ,s1.[Total]
      ,s1.[LeagueWeekId]
      ,s1.[LeagueId]
  FROM  [someview] s1 left join  [someview] s2
			on s1.VenueID = s2.VenueID 
			AND s1.LeagueID = @leagueID
Where s2.LeagueID != @leagueID

Join the view to itself on VenueID, add criteria to the join for a particular LeagueID (or remove it) but make sure that right side of join does not have same LeagueID as left side, thus ensuring that the listing would not have a venue from a non-matching league?

Poor planning on your part does not constitute an emergency on my part.

CreativeNRG
Starting Member

44 Posts

Posted - 2008-03-17 : 03:10:14

quote:
Originally posted by dataguru1971

Maybe something like this:

SELECT s1.VenueID
,s1.[Total]
,s1.[LeagueWeekId]
,s1.[LeagueId]
FROM [someview] s1 left join [someview] s2
on s1.VenueID = s2.VenueID
AND s1.LeagueID = @leagueID
Where s2.LeagueID != @leagueID

Join the view to itself on VenueID, add criteria to the join for a particular LeagueID (or remove it) but make sure that right side of join does not have same LeagueID as left side, thus ensuring that the listing would not have a venue from a non-matching league?

Unfortunatly this query gives me all results that do not match the specified league id and not just those that are in league (1) and not in league (2). E.g. the if the above query searched for all venues that are in league 5 and not 6 I would also get those that played in leagues 1,2,3,4,7. I simply want all those that played in one league and not another and am pulling my hair out trying to adapt the query to achieve this. Maybe I cannot see past this?

www.creativenrg.co.uk

CreativeNRG
Starting Member

44 Posts

Posted - 2008-03-27 : 03:07:13

Any help on this would be very much appreciated?

www.creativenrg.co.uk

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2008-03-27 : 16:12:06

Is this what you want -

Select * from [your_view] a
where Not exists (Select 1 from [your_view] where VenueId = a.VenueId and LeagueId != a.LeagueId)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-27 : 16:33:18

Select VenueId FROM YourView GROUP BY VenueId HAVINg COUNT(DISTINCT LeagueId) =1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-27 : 16:48:03

[code]SELECT VenueID
FROM {Your view here}
GROUP BY VenueID
HAVING MIN(LeagueID) = MAX(LeagueID)[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources