sql trouble w/ outer join, etc, blah, blah...

From: Mike <chunchma_at_comcast.net>
Date: 11 Jan 2003 08:20:51 -0800
Message-ID: <77f4bfa6.0301110820.3d7411d6_at_posting.google.com>

First, I'm NO sql guru...just a guy trying to get by. That said, is this possible?!? I have two tables (example below). I want to create a search results that returns all records if no search criteria is chosen, but only returns matches if a search criteria is chosen.

CLIENTS

---

ID
CLIENT_NAME
LOCATION_ID CLIENT_CASES

---

ID
CLIENT_ID
CASE_STATUS To further illustrate, if I do not choose a search criteria, I would get the following records:

1 | Client1 | Loc1 | Case1 | 1 | Open
1 | Client1 | Loc1 | Case2 | 1 | Closed
1 | Client1 | Loc1 | Case3 | 1 | Open
2 | Client2 | Loc2 | 
2 | Client2 | Loc2 | 
2 | Client2 | Loc2 | 

Notice, that Client2 does NOT have any cases in the cases table, but is returned since I'm showing all records from both tables. That part I get...I did it with an outer join. Now, here's what's tripping me up. IF I search for a CASE_STATUS = Open, I only want to show the following:

1 | Client1 | Loc1 | Case1 | 1 | Open
1 | Client1 | Loc1 | Case3 | 1 | Open

And what I'm getting is this:

1 | Client1 | Loc1 | Case1 | 1 | Open
1 | Client1 | Loc1 | Case3 | 1 | Open
2 | Client2 | Loc2 | 
2 | Client2 | Loc2 | 
2 | Client2 | Loc2 | 

How can I filter out the records with null values IF I enter a search criteria?!? Any help would be greatly appreciated. Received on Sat Jan 11 2003 - 17:20:51 CET