Posted by Razvan Socol on 03/06/06 09:49

Hi, 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

[Back to original message]