db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stanley Bradbury <Stan.Bradb...@gmail.com>
Subject Re: Views in Derby
Date Wed, 27 Jun 2007 22:13:13 GMT
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





Mime
View raw message