|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL search for similar records with different datessimilar 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 Try:
SELECT Year , Name , Address FROM MyTable WHERE ( Year = 2005 AND Year = 2006 ) -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous Show quote "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 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: Show quote > 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. Show quote "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 > 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: Show quote > 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 > > I should add that the example you gave is a form of relational division. I
still get confused with the different ways of doing it. That's why I always link to Joe Celko's explanation instead of trying to explain it myself. http://www.dbazine.com/ofinterest/oi-articles/celko1 Show quote "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 > > > > You're right. My reply was hasty and untested. I'm glad that it was 'fleshed
out'. -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous Show quote "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 > out of curiosity, what if AAA moves from 12 central St to 23 Elm St from one
year to the next? Show quote "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 > Slowly changing dimensions?
Denis the SQL Menace http://sqlservercode.blogspot.com/ Jim Underwood wrote: Show quote > 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 > >  |
|||||||||||||||||||||||
Other interesting topics