|
|
Posted by Roy Harvey on 10/05/06 16:05
SELECT a.GID, a.Parcel, a.Name,
MAX(b.Acres) as Acres
FROM Table1 a
LEFT OUTER
JOIN Table2 b
ON a.GID = b.GID
GROUP BY a.GID, a.Parcel, a.Name
Or:
SELECT a.GID, a.Parcel, a.Name,
(select MAX(b.Acres) from Table2 b
where a.GID = b.GID) as Acres
FROM Table1 a
You could use MIN rather than MAX, of course, but you need some basis
for choosing the one value of Acres to display.
Roy Harvey
Beacon Falls, CT
On Thu, 05 Oct 2006 13:49:26 GMT, "MVM" <nospam@nospam.org> wrote:
>Hi,
>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. What I want is one instance of every record
>from Table 1 even if there isn't a record in Table 2; and even if there are
>multiple records in Table 2.
>
>Table 1:
>GID
>Parcel
>Name
>Address
>
>Table 2:
>GID
>Acres
>
>
>I tried a left outer join, but it still returns multiple results because
>there are multiple records for the same GID in table 2.
>
>SELECT a.GID, a.Parcel, a.Name, b.Acres from
>Table1 a LEFT OUTER JOIN Table2 b
>ON a.GID = b.GID
>
>
>I also tried using the DISTINCT keyword, but the problem is that the Acres
>are different for every record in Table 2.
>
>What query can I run to achieve my desired result?
>
>Thanks a bunch,
>MVM
>
Navigation:
[Reply to this message]
|