Re: SQL question..

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 -
Received on Wed Feb 07 2007 - 15:07:06 CST