|
|
Posted by Dave on 03/06/06 04:07
Hi all,
I have 2 tables 'ZoneData' and 'ZoneUser'. The 'ZoneUser' table has a
column that refers
to a 'ZoneData' row. Table definitions and sample data are:
CREATE TABLE [dbo].[ZoneData](
[ZoneId] [int] NOT NULL,
[ZoneName] [nchar](10) NOT NULL,
[IsDefault] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ZoneUser](
[Id1] [int] NOT NULL,
[Id2] [int] NOT NULL,
[ZoneId] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,4)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,1)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (102,3,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (103,4,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,5)
GO
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (1,'Zone 1',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (2,'Zone 2',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (3,'Zone 3',1)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (4,'Zone 4',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (5,'Zone 5',0)
GO
Running the Query:
SELECT ZoneUser.*, ZoneData.IsDefault FROM ZoneData INNER JOIN ZoneUser
ON ZoneData.ZoneId = ZoneUser.ZoneId
Displays the data:
Id1 Id2 ZoneId IsDefault
100 1 3 1
100 1 2 0
100 1 4 0
101 2 5 0
101 2 1 0
101 2 3 1
102 3 5 0
103 4 3 1
104 5 2 0
104 5 5 0
For each combination of 'Id1' and 'Id2' there may be 0 or more rows
with different 'ZoneId' values.
The Problem: I would like to create a query that could return a row for
every
'Id1' and 'Id2' combination that showed the FIRST (in terms of 'first
found in
database' - not as a result of some sort order) row where 'IsDefault ==
0'
Using the above data, the output would be:
100 1 2 0 < ZoneId '2' id 1st row where 'IsDefault # 1'
101 2 5 0
102 3 5 0
104 5 2 0
* There is no row returned for Id1=103 Id2=4 as there is no row where
'IsDefault = 0'
Has anyone got an idea on how I might do this? I'm using SQL Server
2005
cheers,
dave
[Back to original message]
|