Selecting results based on a specific criteria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting results based on a specific criteria

Author

Topic

Spetty
Starting Member

25 Posts

Posted - 2006-12-08 : 12:12:36

basically I have two tables, one for client information, and one for patient information. Clients can have multiple patients.

Some of these patients can be marked deceased. How can I go about getting results that will select only clients who have patients that are all deceased using a query?

Basically I need to have it check each line....if the client number matches a patient that is deceased and then sees that they have a live patient, it will not return that row.

Any help or input would be great!

Thanks
Shawn

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-08 : 12:18:31

You need to post your table structure if you want help with specific tables otherwise we can't show you what the exact query will be. Here's the basic idea though

SELECT *
FROM Client
WHERE NOT EXISTS (SELECT *
FROM Patient
WHERE Patient.ClientID = Client.ClientID AND Patient.IsDeceased = 1)

Spetty
Starting Member

25 Posts

Posted - 2006-12-08 : 12:59:05

Client Table - client.number
Patient Table - patient.number, patient.client, patient.hospitalized (E marks Euthanized)

there are other fields, but these are really the only major players in what i'm wanting to do.
I just need to make sure that clients are selected that do NOT have a Euthanized patient AND an active (empty/null value in hospitalized).

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-08 : 13:11:56

OK, so fill in the blanks

SELECT *
FROM Client
WHERE NOT EXISTS (SELECT *
FROM Patient
WHERE Patient.Client = Client.Number AND Patient.Hospitalized = 'E')

Spetty
Starting Member

25 Posts

Posted - 2006-12-08 : 23:11:20

quote:
Originally posted by snSQL

OK, so fill in the blanks

SELECT *
FROM Client
WHERE NOT EXISTS (SELECT *
FROM Patient
WHERE Patient.Client = Client.Number AND Patient.Hospitalized = 'E')

Does not work right.

For example:

Client:
Number
1
2
3

Patient:
Number - Client - Hospitalized
50 - 1 - E
51 - 1 - E
52 - 2 - E
53 - 2 - ''
54 - 3 - ''

The only result is should be returning is Client #1 as it is the only client account where the patients have been euthanized. The statement as you wrote it is still returning me a majority of clients in the database (93 to be exact) when only one account has all patients euthanized. There are only 5 total 'E' patients in this test DB altogether.

Shawn

Spetty
Starting Member

25 Posts

Posted - 2006-12-12 : 12:35:34

An Update:

Managed to get it squared away.

Here is the actual statement used:

select DISTINCT from client inner join animal on client.number =
animal.client where not exists (select * from animal where
client.number = animal.client and animal.hospitalized <> 'E')

Thanks for the help with the theory on how to do this.

Shawn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources