Home » SQL & PL/SQL » SQL & PL/SQL » help with joining quries
help with joining quries [message #37038] Fri, 11 January 2002 07:09 Go to next message
Lance Pris
Messages: 40
Registered: January 2002
Member
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
Re: help with joining quries [message #37043 is a reply to message #37038] Fri, 11 January 2002 12:25 Go to previous messageGo to next message
vk
Messages: 13
Registered: December 2001
Junior Member
SELECT
a.pc_solution_id, b.pc_session_user_name,c.pc_incident_id

FROM
pt_solution a,
pt_solution_event b,
pt_incidentlink c

WHERE
a.pc_secure_id = b.pc_sol_secure_id and
a.pc_solution_uuid = c.pc_solution_uuid and
a.pc_solution_id = 'sk4968';
Re: help with joining quries [message #37046 is a reply to message #37043] Fri, 11 January 2002 12:37 Go to previous message
Lance Pris
Messages: 40
Registered: January 2002
Member
Thank you for you help but if you see in my problem this issue is not that I do not know how to join the table but in the result set be exponential.

If I use the query that you posted it will return 48 records where it should be only eight but because of the relationship in the tables, it occurs.

The way that I got around the problem is to create a view setting values to NULL but now I am at a point were my view looks simler to this and I do not know how to extract the data.

How do I link them up to return a result in one row.

Similar to this:
LOCATION_ID Department Warehouse
----------- --------------------- --------------------------
1400 IT
1400 Southlake, Texas
1500 Shipping
1500 San Francisco
1600 New Jersey
1700 Accounting
1700 Administration
1700 Benefits
1700 Construction

I am confused about this any help would be appreciated.

Thank you
Lance

Thank you
Lance
Previous Topic: Re: Functions and Views
Next Topic: Re: Functions and Views
Goto Forum:
  


Current Time: Thu Mar 28 21:16:12 CDT 2024