November 30, 2021 at 8:34 pm
I have a table with the below data. ID is unique number. There are 3 records associated with John but I dont want John to be returned as one of John's records has a status of 1. I would like both Mary's and Jens records to return as none of Mary's and Jens records has a status of 1.
Thanks for your help.
ID SubID Name Address status
1 100 John 123 main 1
2 100 John PO box 100 2
3 100 John 555 fake 2
4 200 Mary 890 Hills 2
5 200 Mary 890 Hills 2
6 300 Jen Po Box 3
Expected Result with columns
SubID Name Address status
200 Mary 890 Hills 2
200 Mary 400 county 2
300 Jen Po Box 3
November 30, 2021 at 9:16 pm
How about a not in query?
SELECT 1 as id, 100 as subID, 'John' as [name], '123 main' as [address], 1 as status into #t
UNION SELECT 2,100,'John', 'PO box 100', 2
UNION SELECT 3,100,'John', 'fake', 2
UNION SELECT 4,200,'Mary','890 Hills',2
UNION SELECT 5,200,'Mary','890 Hills',2
UNION SELECT 6,300,'Jen','Po Box', 3
SELECT * FROM #t t
WHERE subID NOT IN (SELECT subID from #t where status = 1)
November 30, 2021 at 10:20 pm
Also try this version, which is the way I would usually do it.
SELECT * FROM #t t
WHERE NOT EXISTS (SELECT * from #t t2 where t2.status = 1 and t2.subID = t.subID)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 1, 2021 at 1:35 am
That's a much better suggestion, Phil. Thank you.
December 2, 2021 at 8:59 pm
Thank you for your solution.
status = 1 means approved.
I have a new requirement that should show all the approved and unapproved (status1,2,3). In John's case since he has the status of 1 for at least one record, I want to display the one that has status=1 and skip all records related to John for that SubID.
In Mary and Jen's case since none has status 1 for any SubID, they should all show up.
Expected result is
Name Address status
John 123 Main 1
Mary 890 Hills 2
Mary 400 county 2
Jen Po Box 3
Thanks for your help.
December 2, 2021 at 9:26 pm
>>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
REFERENCES Clients(client_id),
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply