help with joining quries [message #37038] |
Fri, 11 January 2002 07:09 |
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 |
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 |
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
|
|
|