Newbie - Help required with Query (sample tables/data included)

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:

Id1Id2ZoneIdIsDefault
100131
100120
100140
101250
101210
101231
102350
103431
104520
104550

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:

100120 < ZoneId '2' id 1st row where 'IsDefault # 1'
101250
102350
104520

* There is no row returned for Id1=103Id2=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,
daveHi, Dave

Your DDL has no primary keys, foreign keys or unique constraints.
This is a serious mistake, because:
1. It allows bad data to be entered in the tables
2. It prevents us from understanding the meaning of your tables, so we
cannot provide a good answer without them.

I assume the following constraints:
ALTER TABLE ZoneData ADD PRIMARY KEY (ZoneId), UNIQUE (ZoneName)
ALTER TABLE ZoneUser ADD UNIQUE (ZoneId, Id1)
ALTER TABLE ZoneUser ADD FOREIGN KEY (ZoneId) REFERENCES ZoneData

There is no such thing as "first found in database". By definition,
tables are unordered sets of rows. We have to use a sort criteria to
specify which is the first row.

First time I read your message, I believed you wanted something like
this:

SELECT U.*, D.IsDefault
FROM ZoneData D
INNER JOIN ZoneUser U ON D.ZoneId = U.ZoneId
INNER JOIN (
SELECT ZoneId, MIN(Id1) as MinOfId1
FROM ZoneUser GROUP BY ZoneId
) X ON U.ZoneId=X.ZoneId AND U.Id1=MinOfId1
WHERE D.IsDefault=0

The above query returns the following results:

Id1 Id2 ZoneId IsDefault
---- ---- ---- ---
101 2 1 0
100 1 2 0
100 1 4 0
101 2 5 0
(4 row(s) affected)

Are you sure you don't want these results instead of what you wrote ?

If you are sure, I'm going to try writing another query that will
return what you wrote (but it doesn't have a lot of sense). Maybe you
will tell us what Id1 and Id2 mean, so we can better understand what
you want to do.

Razvan|||Hi Razan,

Thanks for your reply. Your comment re 'database order' not existing
has me thinking perhaps my concept of what I want to do may be wrong. I
will consider your reply in detail to see where I might have 'got
lost'. Thanks you for taking the time to explain this.

cheers,
dave|||Do not use assembly language style bit flags in a high level language
like SQL. Use a sequence number for zones, if the zone-id will not do
the job. All relationships have to be expressed as values in columns
in tables. You never refer to the physical storage in a quiery.

CREATE TABLE Zones
(zone_id INTEGER NOT NULL PRIMARY KEY
zone_name CHAR(10) NOT NULL,
zone_rank INTEGER DEFAULT 0 NOT NULL
CHECK (zone_rank > 0),
UNIQUE (zone_id, zone_rank))
);

CREATE TABLE ZoneUsers
(user_id_1 INTEGER NOT NULL,
user_id_2 INTEGER NOT NULL,
PRIMARY KEY (user_id_1, user_id_2),
zone_id INTEGER NOT NULL
REFERENCES Zones(zone_id)
);

SELECT U.user_id_1, U.user_id_2, U.zone_id, MIN(Z.zone_rank)
FROM ZoneUsers AS U, Zones AS Z
WHERE Z.zone_id = U.zone_id
GROUP BY U.user_id_1, U.user_id_2, U.zone_id;|||Thanks for the comments. Looks like I have some work to do!

cheers,
dave|||After 20+ years of SQL, I tell people it takes one year of full-time
programming with college -level education to be an SQL programmer.
This is cheap; it takes 6 yers to become a Union Journeyman Carpenter
in New York State.

A bad ptrogrammer can kill or maim a lot more people than a bad
carpenter.|||>A bad ptrogrammer can kill or maim a lot more people than a bad
carpenter.

Oh?? I'll bite. How does a programmer kill or maim a lot of people?|||"Doug" <drmiller100@.hotmail.com> wrote in message
news:1141706342.190761.230170@.j33g2000cwa.googlegr oups.com...
> >A bad ptrogrammer can kill or maim a lot more people than a bad
> carpenter.
> Oh?? I'll bite. How does a programmer kill or maim a lot of people?

One of several ways.

There's a recent case in Panama where a radiological machine used to deliver
doses of radiation to kill cancer was improperly used and killed a number of
patients. Besides the techs being indicted there was at least talk of
bringing the programmers to court since they wrote the software that
permitted the misuse of the machine w/o proper feedback.

http://www.findarticles.com/p/artic...3/ai_ziff120920

Or imagine the case of the Shuttle Software (which is among the most
"perfect" ever written) where a condition was found (preflight fortunately)
that locked up the shuttle arm. Evidently the programmer made a simply
mistake and assumed that its rotational functionality extended from 0 to 360
degrees rather than 1-360 or 0-359. A search found a couple of other places
where a similar error (i.e. overrunning by 1) was in the code.

In the case of the arm, they could have jestisoned it. In the case of a
botched landing, a similar error could have crashed the shuttle.

It's not hard to imagine extended such errors to avionics software or
software controlling a nuclear reactor, etc.