Re: SQL question..

From: Martin Gäckler <martin_at_gaeckler.de>
Date: Wed, 07 Feb 2007 10:46:30 +0100
Message-ID: <eqc75q$p73$1@online.de>

redrosekrs_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) "5 Lincoln Ave"
>
> 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 = '5 Lincoln Ave') 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 = "5 lincoln ave"?
>

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 = '5 Lincoln Ave'

)

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 = '5 Lincoln Ave'

)

Hope this helps

Martin

-- 
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

Received on Wed Feb 07 2007 - 03:46:30 CST