"Stanhelp" <stanh***@gmail.com> wrote in message news:1158162880.660097.19920@h48g2000cwc.googlegroups.com...
> I've created a query that joins 3 tables that needs to display only
> similar records that are in year 2005 and 2006.
>
> Current Example Results Data:
> Year    Name      Address
> ------------------------------------------------
> 2005    ABC       95 Main St.
> 2005    AAA       12 Central St.
> 2006    AAA       12 Central St.
> 2005    BBB       3 Thank You Rd.
> 2006    BBB       3 Thank You Rd.
>
> I don't want Name:ABC to be part of the data I get back.
> I get these results because I use ->Where Year BETWEEN '2005' AND
> '2006'
>
> What I would like to see is:
> Year    Name      Address
> ------------------------------------------------
> 2005    AAA       12 Central St.
> 2006    AAA       12 Central St.
> 2005    BBB       3 Thank You Rd.
> 2006    BBB       3 Thank You Rd.
>
> And even better would be:
> Year    Name      Address
> ------------------------------------------------
> 2005-2006    AAA       12 Central St.
> 2005-2006    BBB       3 Thank You Rd.
>
> Thank you for taking the time to help me.
> Stan
> stanh***@gmail.com
>

> Arnie,
>
> your query will return nothing no matter what.
>
> SELECT
>      '2005-2006' Year
>    , y2005.Name
>    , y2005.Address
> FROM MyTable y2005 JOIN MyTable y2006 ON y2005.Name = y2006.Name AND
> y2005.Address = y2006.Address
> WHERE y2005.Year = 2005
>       AND Y2006.Year = 2006

"SQL Menace" <denis.g***@gmail.com> wrote in message
news:1158165998.102584.290030@i3g2000cwc.googlegroups.com...
> I think that this should also work (assuming 1 row per year)
>
> SELECT      Year    , Name    , Address
> FROM MyTable
> WHERE (   Year = 2005
>       OR Year = 2006
>       )
> group by Year    , Name    , Address
> having count(*) = 2
>
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>
> Alexander Kuznetsov wrote:
> > Arnie,
> >
> > your query will return nothing no matter what.
> >
> > SELECT
> >      '2005-2006' Year
> >    , y2005.Name
> >    , y2005.Address
> > FROM MyTable y2005 JOIN MyTable y2006 ON y2005.Name = y2006.Name AND
> > y2005.Address = y2006.Address
> > WHERE y2005.Year = 2005
> >       AND Y2006.Year = 2006
>

> I think you need to remove Year from your select list and group by,
> otherwise you will get two rows instead of one.  Changing the OR to an IN
> clause might save some typing and make it more friendly to the eye.
>
> Relational division would also work here, but is a little more complicated.
>
> As long as it is only two years, the suggestions here are much simpler.
>
> The suggestion below would work no matter how many years you have, however.
>
>
> "SQL Menace" <denis.g***@gmail.com> wrote in message
> news:1158165998.102584.290030@i3g2000cwc.googlegroups.com...
> > I think that this should also work (assuming 1 row per year)
> >
> > SELECT      Year    , Name    , Address
> > FROM MyTable
> > WHERE (   Year = 2005
> >       OR Year = 2006
> >       )
> > group by Year    , Name    , Address
> > having count(*) = 2
> >
> >
> > Denis the SQL Menace
> > http://sqlservercode.blogspot.com/
> >
> >
> > Alexander Kuznetsov wrote:
> > > Arnie,
> > >
> > > your query will return nothing no matter what.
> > >
> > > SELECT
> > >      '2005-2006' Year
> > >    , y2005.Name
> > >    , y2005.Address
> > > FROM MyTable y2005 JOIN MyTable y2006 ON y2005.Name = y2006.Name AND
> > > y2005.Address = y2006.Address
> > > WHERE y2005.Year = 2005
> > >       AND Y2006.Year = 2006
> >

"SQL Menace" <denis.g***@gmail.com> wrote in message
news:1158167502.787131.140180@m73g2000cwd.googlegroups.com...
> Jim,
>
> Yes you are right, year has to be removed, if not count(*) can never be
> true (unless there is more than 1 row per year)
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>
> Jim Underwood wrote:
> > I think you need to remove Year from your select list and group by,
> > otherwise you will get two rows instead of one.  Changing the OR to an
IN
> > clause might save some typing and make it more friendly to the eye.
> >
> > Relational division would also work here, but is a little more
complicated.
> >
> > As long as it is only two years, the suggestions here are much simpler.
> >
> > The suggestion below would work no matter how many years you have,
however.
> >
> >
> > "SQL Menace" <denis.g***@gmail.com> wrote in message
> > news:1158165998.102584.290030@i3g2000cwc.googlegroups.com...
> > > I think that this should also work (assuming 1 row per year)
> > >
> > > SELECT      Year    , Name    , Address
> > > FROM MyTable
> > > WHERE (   Year = 2005
> > >       OR Year = 2006
> > >       )
> > > group by Year    , Name    , Address
> > > having count(*) = 2
> > >
> > >
> > > Denis the SQL Menace
> > > http://sqlservercode.blogspot.com/
> > >
> > >
> > > Alexander Kuznetsov wrote:
> > > > Arnie,
> > > >
> > > > your query will return nothing no matter what.
> > > >
> > > > SELECT
> > > >      '2005-2006' Year
> > > >    , y2005.Name
> > > >    , y2005.Address
> > > > FROM MyTable y2005 JOIN MyTable y2006 ON y2005.Name = y2006.Name AND
> > > > y2005.Address = y2006.Address
> > > > WHERE y2005.Year = 2005
> > > >       AND Y2006.Year = 2006
> > >
>

"Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
news:1158165732.193798.21120@h48g2000cwc.googlegroups.com...
> Arnie,
>
> your query will return nothing no matter what.
>
> SELECT
>     '2005-2006' Year
>   , y2005.Name
>   , y2005.Address
> FROM MyTable y2005 JOIN MyTable y2006 ON y2005.Name = y2006.Name AND
> y2005.Address = y2006.Address
> WHERE y2005.Year = 2005
>      AND Y2006.Year = 2006
>

"Stanhelp" <stanh***@gmail.com> wrote in message
news:1158162880.660097.19920@h48g2000cwc.googlegroups.com...
> I've created a query that joins 3 tables that needs to display only
> similar records that are in year 2005 and 2006.
>
> Current Example Results Data:
> Year    Name      Address
> ------------------------------------------------
> 2005    ABC       95 Main St.
> 2005    AAA       12 Central St.
> 2006    AAA       12 Central St.
> 2005    BBB       3 Thank You Rd.
> 2006    BBB       3 Thank You Rd.
>
> I don't want Name:ABC to be part of the data I get back.
> I get these results because I use ->Where Year BETWEEN '2005' AND
> '2006'
>
> What I would like to see is:
> Year    Name      Address
> ------------------------------------------------
> 2005    AAA       12 Central St.
> 2006    AAA       12 Central St.
> 2005    BBB       3 Thank You Rd.
> 2006    BBB       3 Thank You Rd.
>
> And even better would be:
> Year    Name      Address
> ------------------------------------------------
> 2005-2006    AAA       12 Central St.
> 2005-2006    BBB       3 Thank You Rd.
>
> Thank you for taking the time to help me.
> Stan
> stanh***@gmail.com
>

> out of curiosity, what if AAA moves from 12 central St to 23 Elm St from one
> year to the next?
>
> "Stanhelp" <stanh***@gmail.com> wrote in message
> news:1158162880.660097.19920@h48g2000cwc.googlegroups.com...
> > I've created a query that joins 3 tables that needs to display only
> > similar records that are in year 2005 and 2006.
> >
> > Current Example Results Data:
> > Year    Name      Address
> > ------------------------------------------------
> > 2005    ABC       95 Main St.
> > 2005    AAA       12 Central St.
> > 2006    AAA       12 Central St.
> > 2005    BBB       3 Thank You Rd.
> > 2006    BBB       3 Thank You Rd.
> >
> > I don't want Name:ABC to be part of the data I get back.
> > I get these results because I use ->Where Year BETWEEN '2005' AND
> > '2006'
> >
> > What I would like to see is:
> > Year    Name      Address
> > ------------------------------------------------
> > 2005    AAA       12 Central St.
> > 2006    AAA       12 Central St.
> > 2005    BBB       3 Thank You Rd.
> > 2006    BBB       3 Thank You Rd.
> >
> > And even better would be:
> > Year    Name      Address
> > ------------------------------------------------
> > 2005-2006    AAA       12 Central St.
> > 2005-2006    BBB       3 Thank You Rd.
> >
> > Thank you for taking the time to help me.
> > Stan
> > stanh***@gmail.com
> >