Re: SQL question..

On Feb 7, 1:07 pm, redrose..._at_yahoo.com wrote:
> On Feb 7, 1:46 am, Martin Gäckler <mar..._at_gaeckler.de> wrote:
>
>
>
> > redrose..._at_yahoo.com schrieb:
>
> > > I want to find out the phone and fax for all employees who work at a
> > > certain address.. but I don't know if the db is configured to give me
> > > this information easily.
>
> > > RESULTS.NAME is the name of the field ex) "Address" and RESULTS.VALUE
> > > is the actual value ex) "5LincolnAve"
>
> > > A typical record has more than 1 RESULTS.VALUE (555-555-5555,
> > > 457-457-4577, 1 ABC street) each with their own individual
> > > RESULTS.NAME (phone, fax, address etc..)
>
> > > SELECT RESULTS.ID, RESULTS.VALUE, RESULTS.DATE_ENTERED,
> > > PROJECTS.PROJECTID, RESULTS.NAME FROM RESULTS, PROJECTS WHERE
> > > (PROJECTS.PRODUCT = 'DT') AND (RESULTS.DATE_ENTERED < {ts '2007-02-05
> > > 10:13:00'}) AND (RESULTS.VALUE = '5LincolnAve') AND (RESULTS.NAME
> > > ='phone') AND (RESULTS.NAME = 'fax') AND (RESULTS.ID = PROJECTS.ID)
> > > ORDER BY RESULTS.ID ASC (doesn't work)
>
> > > This query brings up nothing because the RESULTS.NAME and
> > > RESULTS.VALUE don't coincide. Is there a way to get several
> > > RESULTS.VALUE's for several RESULT.NAME's for all records matching the
> > > case where RESULT.VALUE = "5lincolnave"?
>
> > select r1.name, r1.value
> > from results r1
> > where r1.name in ( 'phone', 'fax' )
> > and exists (
> > select *
> > from results r2
> > where r1.id=r2.id
> > and r2.name = 'address'
> > and r2.value = '5LincolnAve'
> > )
>
> > or
>
> > select r1.name, r1.value
> > from results r1
> > where r1.name in ( 'phone', 'fax' )
> > and r1.id in (
> > select r2.id
> > from results r2
> > where r2.name = 'address'
> > and r2.value = '5LincolnAve'
> > )
>
> > Hope this helps
>
> > Martin
>
> > --
>
> I don't know what EAV is? This database is horrible in my opinion, but
> luckily I'm finding how to navigate through it to find my little
> queries. I didn't try your suggestion but thanks anyway Chas, as I
> immediately skipped to Martin's second suggestion (it just looked so
> simple!) And, it worked!
>
> Thanks for all of the suggestions!
>
>
>
> > Firma/Company: CRESD GmbH
> > Phone: +49-89-65 30 95 63 Fax: +49-89-65 30 95 64
> > WWW: http://www.cresd.de
> > S-Mail: Freibadstr. 14, D-81543 Muenchen
>
> > Geschaeftsfuehrer: Christina Ragg, Martin Gaeckler
> > Steuernr. - USt. ID: 143/127/10030 - DE218181906
> > Handelsregisterblatt: Muenchen 137833
>
> > PGP-Key: http://www.cresd.de/edv/pgpkey.txt
> > Open BC (Einladung) http://www.openbc.com/go/invita/4561755-Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Arlene,

You can get a good overview of E-A-V (entity-attribute-value) here: http://oracle-wtf.blogspot.com/2006/02/eav-returns-concrete-elephant-approach.html

REgards,
Chas. Received on Wed Feb 07 2007 - 22:05:04 CST