| What is wrong with my logic? [message #39011] |
Thu, 06 June 2002 09:35  |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
Hi,
I have three tables: cust c,
cust_prof cp,
case_par gcp.
If case_par is empty I need to select data from two first tables (full extract). But if case_par has any records (usually few) I have to join two first tables with case_par (limited extract).
I tried to do it in one select:
SELECT DISTINCT
-- c.cust_id,
c.prt_id,
c.name_last,
c.name_first,
cp.case_cust_id,
cp.case_id_nmbr,
cp.cust_type,
cp.payee_status
FROM cust c,
cust_prof cp,
case_par
WHERE (c.c_id = cp.c_id --Part 1
AND NOT EXISTS (SELECT rowid FROM case_par) )
OR (c.c_id = cp.c_id --Part 2
AND cp.case_id=gcp.case_id);
When case_par has records it works (part 2), but when it does not, the query returns no results (part 1)
I am still working on it, but if you let me know
what is wrong with my logic I will appriciate it.
Thank you
|
|
|
|
| Re: What is wrong with my logic? [message #39012 is a reply to message #39011] |
Thu, 06 June 2002 10:07  |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
Try a union:
SELECT ...
FROM cust c
,cust_prof cp
,case_par gcp
WHERE c.c_id = cp.c_id
AND cp_case_id = grc.case_id
AND EXISTS (SELECT 'x' FROM case_par)
UNION
SELECT ...
FROM cust c
,cust_prof cp
WHERE c.c_id = cp.c_id
AND NOT EXISTS (SELECT 'x' FROM case_par)
;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
