Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL question..
On Feb 5, 2:28 pm, redrose..._at_yahoo.com wrote:
> 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"?
You perhaps want ((RESULTS.NAME='phone') OR (RESULTS.NAME='fax))
It also may help to format onto several lines with indentation, and
use aliases.
Aliases are like
SELECT
r.ID, r.VALUE, r.DATE_ENTERED, p.PROJECTID, r.NAME
(r.VALUE = '5 Lincoln Ave') AND ((r.NAME='phone') OR (r.NAME = 'fax')) AND (r.ID = p.ID)
Or whatever formatting you can get everyone to agree on.
jg
-- @home.com is bogus. Exploit the superbowl. http://blogs.zdnet.com/security/?p=15Received on Mon Feb 05 2007 - 18:27:13 CST