|
Posted by MVM on 10/05/06 16:19
Thank you.
"Roy Harvey" <roy_harvey@snet.net> wrote in message
news:u1bai2puocegnubl9n5pdla7v2g40h1q95@4ax.com...
> 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]
|