Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL question..

Re: SQL question..

From: joel garry <joel-garry_at_home.com>
Date: 5 Feb 2007 16:27:13 -0800
Message-ID: <1170721632.969864.7240@v33g2000cwv.googlegroups.com>


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

FROM
  RESULTS r,
  PROJECTS p
WHERE
  (p.PRODUCT = 'DT') AND
  (r.DATE_ENTERED < to_date(
    '2007-02-05 10:13:00', 'YYYY-MM-DD HH:MI:SS')) AND
  (r.VALUE = '5 Lincoln Ave') AND
  ((r.NAME='phone') OR (r.NAME = 'fax'))  AND
  (r.ID = p.ID)

ORDER BY
  r.ID ASC

Or whatever formatting you can get everyone to agree on.

jg

--
@home.com is bogus.
Exploit the superbowl.  http://blogs.zdnet.com/security/?p=15
Received on Mon Feb 05 2007 - 18:27:13 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US