|
|
Posted by --CELKO-- on 10/05/06 14:10
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
>> I am attempting to run a query in MS SQL server between two tables that have a one to many relationship. The tables are linked on GID. <<
First of all, I hope that GID is an industry standard code and not what
I think it is. Next, tables are referenced and not linked -- links are
pointers and are not part of SQL. If you have the right terms, you
have the right mindset.
>> What I want is one instance of every record [sic] from Table 1 even if there isn't a record [sic] in Table 2; and even if there are multiple records in Table 2. <<
Even in a personal narrative pseudo-code example, can't you think of
better table names? I also see that you do not know that rows are
nothing like records. Wrong mindset again. I am also scared that GID
is a GUID or other exposed physical locator and not a relational key at
all.
My guess is that name and address refer to the owner of the parcel --
something your data element did not tell us at all. Furthermore, I
would guess that a parcel has a unique identifier (probably a string
with a grep to validate it, but let's use integer). And since acreage
is an attribute of a parcel, it needs to be in that table (this design
flaw is called attribute splitting and it takes on many forms).
CREATE TABLE Parcels
(parcel_nbr INTEGER NOT NULL PRIMARY KEY
owner_name VARCHAR(35) NOT NULL,
owner_addr VARCHAR(35) NOT NULL,
acreage DECIMAL (6,2) NOT NULL);
>> I tried a left outer join, but it still returns multiple results because there are multiple records [sic] for the same GID in table 2. <<
That is one of many reasons we do not split attributes. Your data
integirty is destroyed. There is no magic query that will restore it.
You cannot just pick the MIN() or MAX() the split table because you
have no idea which one is correct. You now have a to do a full data
audit and go back to the land office records to confirm the acreage
parcel by parcel.
Navigation:
[Reply to this message]
|