Dmitri Pissarenko wrote:
Hello!
I have Table A with following fields:
1) A.Date/time
2) A.Location
In table B for each record of table A there is zero, one or more
records with following fields:
1) B.name
2) B.type
B.type can take on one of exactly four values, say "TYPE1", "TYPE2",
"TYPE3", "TYPE4".
I need to view these data in several ways.
View #1
Fields:
1) A.Date/time
2) A.Location
3) B records with type TYPE1 as string
4) B records with type TYPE2 as string
5) B records with type TYPE3 as string
6) B records with type TYPE4 as string
7) All B records as string
Sort order: 1) Location 2) date/time
For instance:
A.Date/time | A.Location | B records TYPE1 | B records TYPE2 | B
records TYPE3 | B records TYPE4 | All B records
2007-06-27 15:08 | Location 1 | BName1, BName2 | BName3 | BName4 |
BName 5 | BName1, BName2, BName3, BName4, BName 5
View #2
Fields:
1) A.Date/time
2) A.Location
3) All B records as string
Sort order: 1) Location 2) date/time
In this view, I want to see part of the data from View #1, but with a
difference - rows with same content in field 3) should not be shown.
That is, I DON'T want this:
A.Date/time | A.Location | All B records as string
2007-06-27 15:14 | Loc1 | BName1, BName2, BName3
2007-06-27 15:15 | Loc1 | BName1, BName2, BName3
2007-06-27 15:16 | Loc1 | BName1, BName2, BName3
2007-06-27 15:17 | Loc1 | BName1, BName2
But I DO want this:
A.Date/time | A.Location | All B records as string
2007-06-27 15:14 | Loc1 | BName1, BName2, BName3
2007-06-27 15:17 | Loc1 | BName1, BName2
I have two questions:
How to do this in Derby with
a) minimum amount of Java coding and
b) with minimum amount of duplicated data (if I have to use tables,
instead of views for views #1 and #2, then data are being duplicated)
?
Thanks in advance
Dmitri Pissarenko
HI Dmitri -
I don't have a clear understanding of your question but if it is simply
that you are getting the four records you list as 'DON'T want' and want
the two records listed under 'DO want' I think you can do this with a
group-by SQL statement. Try this and let me know if it works:
select min(Date/Time), Location, ALL_as_string
from <myViewName>
group by Location, ALL_as_string
