Re: Help with joining queries

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Fri, 11 Jan 2002 11:50:13 -0800
Message-ID: <F001.003EE1E7.20020111112709@fatcity.com>

Lance,
 I had the same proplem awhile ago. The two tables had a different numbers of columns needed returned so I jioned them in a view and then issued a select against the view with the proper where clause.   CREATE OR REPLACE VIEW camsview AS
  SELECT a.saledate saledate,

  a.retnbr retnbr,
  a.settlementamt insettle,
  a.returnamt inret,
  a.cashamt     incash,
  a.SALESCOMMAMT  incomm,
  a.CASHBONUSAMT  inbonus,
  to_number(null) olsales,
  to_number(null) olcash,

  to_number(null) olcomm,
  to_number(null) olbonus
  from glciwsr a
  union all
  select b.saledate,

  b.retnbr retnbr,
  to_number(null),
  to_number(null),
  to_number(null),
  to_number(null),
  to_number(null),
  b.salesamt olsales,
  b.cashamt olcash,

  b.salescommamt olcomm,
  b.cashbonusamt olbonus
  from glcowsr b;

set pagesize 60
set linesize 130
break on report
compute suReceived: from CONNECT-MTA by galottem of insettle inret incash incomm inbonus on report
compute sum of olsales olcash olcomm olbonus on report select saledate,
(sum(insettle) - sum(inret)) insettle,

sum(inret) inret,
sum(incash) incash,
sum(incomm)*-1 incomm,
sum(inbonus)*-1 inbonus,
sum(olsales) olsales,
sum(olcash)*-1 olcash,
sum(olcomm)*-1 olcomm,
sum(olbonus)*-1 olbonus

from camsview where
retnbr = &retnbr and
saledate between '&start_dt' and '&end_dt' group by saledate;

This allowed the user to enter the desired retnbr and saledate range and get back the data from both tables on a single row. ROR mª¿ªm

>>> lprais_at_ts.checkpoint.com 01/11/02 12:05PM >>>
Here is my situation, I need to join the following two queries:

TABLE RELATIONS:
PT_SOLUTION-----1 to Many----a.PT_SOLUTION_EVENT PT_SOLUTION-----1 to Many----a.PT_INCIDENTLINK

PROBLEM:
When I attempt to join all three tables the result set is x amout of time
that I need for example:

RESULTS:
Query 1returns 8 rows
Query2 returns 6 rows
I attempt to join the result is 48.

I believe using intersect is the best way to resolve this but I am having
problems and am afraid I may be going down the wrong path.

Thank you
Lance

Query 1
SELECT
  a.pc_solution_id, b.pc_session_user_name FROM
  pt_solution a,
  pt_solution_event b
WHERE
  a.pc_secure_id = b.pc_sol_secure_id and   a.pc_solution_id = 'sk4968';

RESULT: sk4968 tchung
sk4968 tchung
sk4968 dtrevino
sk4968 oreiter
sk4968 oreiter
sk4968 oreiter
sk4968 dkotha
sk4968 kwinfield

Query 2
SELECT
  a.pc_solution_id, b.pc_incident_id
FROM
  pt_solution a,
  pt_incidentlink b
WHERE
  a.pc_solution_uuid = b.pc_solution_uuid and   a.pc_solution_id = 'sk4968';

RESULT:

sk4968          1-283108680
sk4968          83362
sk4968          93765
sk4968          93771
sk4968          93802
sk4968          93905

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Lance Prais
  INET: lprais_at_ts.checkpoint.com 

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
  INET: RROGERS_at_galottery.org

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Jan 11 2002 - 13:50:13 CST