Re: SQL search for similar records with different dates

stanh..._at_gmail.com wrote:
> I've created a query that joins 3 tables that need to display only
> similar records that are in year 2005 and 2006.
>
> Current 3 join 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' in my 3 joins.
>
> 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..._at_gmail.com

And you can't post the query?

At first glance something like this might work:

select c.year, a.name, b.address
from a, b, c
where c.id = a.id
and c.id = b.id
and c.year between '2005' and '2006'
and (a.name, b.address) in (select a.name, b.address

                                         from a, b
                                         where b.id = a.id
                                         group by a.name, b.address
                                         having count(*) > 1);

I would not consider that tuned, or necessarily efficient, however absent the actual query you're using it's the best I can do to provide an example. But it should do what you want by eliminating single rows from your output (your dreaded 'ABC' row, for example).

Provide the actual query (presuming you can do so without divulging proprietary information) and a possibly better and more efficient solution may be presented.

David Fitzjarrell Received on Wed Sep 13 2006 - 15:37:39 CDT