>>I have a table with the below data. <<
Where is the DDL? For over 30 years. Standard netiquette on SQL forums has been to post DDL when you want help. It will bother to read the how-to post part of all of these forums, they will tell you this
>> ID is a unique number. <<
No "_id" is a common postfix for an identifier. It is called an attribute property and data modeling and it must be the identifier of something in particular. People who believe in Kabbalah numbers and other magical generic ids are called idiots in SQL slang. It's a very common beginner's mistake because you're coming in from a file system with record numbers. Rows and records are completely different! Likewise, there is no such thing as a generic "_status"; it must be the status of something in particular. This piece of data modeling is a result of the law of identity in logic and is the foundation of all Western thought. Have you had a formal logic course yet?
>> There are 3 records [sic] associated with John but I don't want John to be returned as one of John's records [sic] has a status of 1. I would like both Mary's and Jens records [sic] to return as none of Mary's and Jens records [sic] has a status of 1. <<
What you don't understand yet about the difference between records and rows is that 80 to 90% of the work SQL is done in the DDL. Let's try actually writing some DDL. Remember that a table must have a primary key and not just be a list of punchcard images like you're doing. We have to worry that Mary and Jens have multiple rows with redundant data. Even before we had RDBMS, the goal of databases was to remove redundancy, not increase it like you're doing
CREATE TABLE Clients_Locations
(client_id CHAR(3) NOT NULL
location_id CHAR(3) NOT NULL,
PRIMARY KEY (client_id, location_id),
client_address VARCHAR(35) NOT NULL,
client_status INTEGER NOT NULL DEFAULT 1 CHECK (client_status >0));
Look at the use of check and default constraints on the data. Get in the habit of using them now on small samples. You'll carry it on when you're doing real work.
CREATE TABLE Clients -- limiting you to one and only one name per identifier
(client_id CHAR(3) NOT NULL PRIMARY KEY,
client_name VARCHAR(10) NOT NULL);
I really don't like this particular design, but I will assume there are some reasons for it. Normally this would be a design flaw called "attributes splitting" these two components make up the identifier of some data element, in this case, a client. Therefore, they ought to be part of a single identifier and not split over multiple columns. But there should be no reason for de-normalizing the table of clients.
SELECT C1.location_id, C2.client_name, C1.client_address, client_status
FROM Clients AS C1, Client_Locations AS C2
WHERE C1.client_id = C2.client_id
AND C1.client_satus > 1
AND C1.client_status =
MAX(C2.client_status) OVER (PARTITION BY C2.client_id);
Use the windowed functions to find the largest status code inside each client.
Please post DDL and follow ANSI/ISO standards when asking for help.