March 10, 2023 at 5:48 pm
I have a table where it has id column, along with description.
Based on Group BY ID, I need to verify for each ID we have row exists with Description Apex, SIERRA, BILL.
How to find out the other Id's that does not have row with these description? Apex, SIERRA, BILL.
for example data looks like
ID Amount Description
234 -230.00 Apex
234 -230.00 Tran
234 -230.00 SIERA
234 -230.00 BILL
234 -230.00 ACT
March 10, 2023 at 7:19 pm
EVERY SINGLE TIME you post data you have to be reminded of the best way to post data. I've come to the conclusion that you don't want to learn, you want to have other people do your work for you. Until you show SOME initiative, I'm out.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 11, 2023 at 12:42 am
I think you need to provide some more information about the data in this table. Is there only 1 row per description per ID? What is the primary key of this table? As Drew mentions, there are much better ways to provide the data if you want help writing query code, but I think you also need to describe the situation more.
March 11, 2023 at 1:31 am
Dear ChatGPT
I have a table where it has id column, along with description.
Based on Group BY ID, I need to verify for each ID we have row exists with Description Apex, SIERRA, BILL.
How to find out the other Id's that does not have row with these description? Apex, SIERRA, BILL.
for example data looks like
ID Amount Description
234 -230.00 Apex
234 -230.00 Tran
234 -230.00 SIERA
234 -230.00 BILL
234 -230.00 ACT
Construct the table with test data and the solution in t-sql
DROP TABLE ExampleTable
;
CREATE TABLE ExampleTable (
ID INT,
Amount DECIMAL(10,2),
Description VARCHAR(50)
);
INSERT INTO ExampleTable (ID, Amount, Description)
VALUES (234, -230.00, 'Apex'),
(234, -230.00, 'Tran'),
(234, -230.00, 'SIERA'),
(234, -230.00, 'BILL'),
(234, -230.00, 'ACT'),
(235, 100.00, 'Apex'),
(235, 200.00, 'SIERA'),
(236, 400.00, 'Tran');
SELECT DISTINCT ID
FROM ExampleTable
WHERE ID NOT IN (
SELECT ID
FROM ExampleTable
WHERE Description IN ('Apex', 'SIERA', 'BILL')
);
March 11, 2023 at 1:51 am
Jonathan, I like your enthusiasm, but I'm thinking your query will return IDs that don't have any of the 3 key description values where as the OP sounds like they want IDs that don't have all of the 3 descriptions. Maybe if the OP posts some consumable data, expected output, and queries that they have already tried, we would have a better idea of which solution they were actually looking for.
March 11, 2023 at 3:38 am
Based on Group BY ID, I need to verify for each ID we have row exists with Description Apex, SIERRA, BILL.
Do you mean ID's that contain any of those 3 or IDs that contain all of those 3 (whether or not any others exist)?
For example, the following ID of "1" has Apex and BILL but is missing SIERRA... Does that meet the criteria of 'have row exists with Description Apex, SIERRA, BILL"?
ID Amount Description
1 -230.00 Apex
1 -230.00 Tran
--1 -230.00 SIERRA is missing
1 -230.00 BILL
1 -230.00 ACT
And, yeah... I strongly agree with Drew. You've been asked in the past to provide readily consumable test data along with expected results and have been asked to read and head the article at the first link in my signature line below that tells you one of many ways to do so.
You posted one example that meets some criteria but it's not readily consumable. You didn't post one that does NOT meet the criteria and you didn't even take the time to spell SIERRA the same way. You're apparently unwilling to help us help you and I'm with Drew in being unwilling to help someone that won't take the extra 5 or 10 minutes to do so.
Change your post to include readily consumable data (with SIERRA spelled just one way) for one ID that meets your criteria and at least one does not if you really want an answer. Otherwise, my answer is in words with no code, as well. Something like "Do an INTERSECT with the correct indexes present to find the 'Haves' and use that as an EXCEPT to find the "Have Nots" with no follow up reply.
And, yep... this will be the last time I ask. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2023 at 3:41 am
@Jonathan... that will work if "any" of the 3 are present. I'm thinking the OP only wants to exclude if all 3 are present. But, please, let him explain that.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2023 at 3:24 pm
@Jonathan... that will work if "any" of the 3 are present. I'm thinking the OP only wants to exclude if all 3 are present. But, please, let him explain that.
Yes, could be the OP hasn't made it clear, ChatGPT comes out with a different answer if you do a bit of Prompt Engineering 🤣 to add an "and" to the question.
Based on Group BY ID, I need to verify for each ID we have row exists with Description Apex, SIERRA, BILL.
Based on Group BY ID, I need to verify for each ID we have row exists with Description Apex, SIERRA and BILL.
-- Find IDs without Apex, SIERRA, and BILL descriptions
SELECT DISTINCT ID
FROM ExampleTable
WHERE ID NOT IN (
SELECT ID FROM ExampleTable WHERE Description = 'Apex'
) AND ID NOT IN (
SELECT ID FROM ExampleTable WHERE Description = 'SIERA'
) AND ID NOT IN (
SELECT ID FROM ExampleTable WHERE Description = 'BILL'
);
March 13, 2023 at 3:23 pm
Please quit being rude to people that are trying to help you. Where is the DDL? If you remember the first week of any class or chapter of any book you read, a table, by definition, must have a key. This is the definition, not an option. What you posted was a useless narrative of a non-table. Then on top of that, your column names are so generic as to be useless. There is no such magical Kabbalah number as "id" in RDBMS; it has to be the identifier of something in particular. This is why we call people who use the identity table property as a key "id-iots" at serious SQL meetings.
Please post DDL and follow ANSI/ISO standards when asking for help.
March 13, 2023 at 3:31 pm
SELECT ID
FROM ExampleTable
WHERE Description IN ('Apex', 'BILL', 'SIERRA')
GROUP BY ID
HAVING MAX(CASE WHEN Description = 'Apex' THEN 1 ELSE 0 END) = 0 OR
MAX(CASE WHEN Description = 'BILL' THEN 1 ELSE 0 END) = 0 OR
MAX(CASE WHEN Description = 'SIERRA' THEN 1 ELSE 0 END) = 0
--ORDER BY ID
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 13, 2023 at 3:55 pm
SELECT ID
FROM ExampleTable
GROUP BY ID
HAVING MAX(CASE WHEN Description = 'Apex' THEN 1 ELSE 0 END) = 0 OR
MAX(CASE WHEN Description = 'BILL' THEN 1 ELSE 0 END) = 0 OR
MAX(CASE WHEN Description = 'SIERRA' THEN 1 ELSE 0 END) = 0
--ORDER BY ID
Very good, much better than ChatGPT:
DROP TABLE ExampleTable
;
CREATE TABLE ExampleTable (
ID INT,
Amount DECIMAL(10,2),
Description VARCHAR(50)
);
;WITH CTE AS
(
SELECT *
FROM (VALUES (234, -230.00, 'Apex'),
(234, -230.00, 'Tran'),
(234, -230.00, 'SIERA'),
(234, -230.00, 'BILL'),
(234, -230.00, 'ACT'),
(235, 100.00, 'Apex'),
(235, 200.00, 'SIERA'),
(236, 400.00, 'Tran')) T(ID, Amount, Description)
)
INSERT INTO ExampleTable (ID, Amount, Description)
SELECT ID+3*N ID, Amount, Description
FROM CTE
CROSS JOIN dbo.fnTally(1,10000) T2
set statistics io, time on
-- Find IDs without Apex, SIERRA, and BILL descriptions
DROP TABLE IF EXISTS #x
DROP TABLE IF EXISTS #y
GO
SELECT DISTINCT ID
INTO #x
FROM ExampleTable
WHERE ID NOT IN (
SELECT ID FROM ExampleTable WHERE Description = 'Apex'
) AND ID NOT IN (
SELECT ID FROM ExampleTable WHERE Description = 'SIERA'
) AND ID NOT IN (
SELECT ID FROM ExampleTable WHERE Description = 'BILL'
);
go
SELECT ID
INTO #y
FROM ExampleTable
GROUP BY ID
HAVING MAX(CASE WHEN Description = 'Apex' THEN 1 ELSE 0 END) = 0 AND
MAX(CASE WHEN Description = 'BILL' THEN 1 ELSE 0 END) = 0 AND
MAX(CASE WHEN Description = 'SIERRA' THEN 1 ELSE 0 END) = 0
--ORDER BY ID
NOT IN
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'ExampleTable'. Scan count 10, logical reads 102093, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1672 ms, elapsed time = 1681 ms.
HAVING MAX(CASE
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'ExampleTable'. Scan count 1, logical reads 299, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 45 ms.
March 13, 2023 at 5:34 pm
I am assuming that the foobars have a key made of the foo_description in the foo_id.
SELECT foobar_id
FROM Foobars
WHERE foobar_description IN ( 'Apex', 'Bill', 'Sierra' )
GROUP BY foobar_id
HAVING COUNT (*) = 3;
Please post DDL and follow ANSI/ISO standards when asking for help.
March 13, 2023 at 6:18 pm
I am assuming that the foobars have a key made of the foo_description in the foo_id.
SELECT foobar_id
FROM Foobars
WHERE foobar_description IN ( 'Apex', 'Bill', 'Sierra' )
GROUP BY foobar_id
HAVING COUNT (*) = 3;
Perhaps if you'd practice what you preach, you'd have discovered the typical rookie failure in your untested code.
DROP TABLE IF EXISTS Foobars;
GO
SELECT d.*
INTO Foobars
FROM (VALUES
(234,'Apex')
,(234,'Apex')
,(234,'Apex')
)d(foobar_id,foobar_description)
;
SELECT foobar_id
FROM Foobars
WHERE foobar_description IN ( 'Apex', 'Bill', 'Sierra' )
GROUP BY foobar_id
HAVING COUNT (*) = 3;
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2023 at 9:53 pm
Yes, in theory you should be able to use just a WHERE clause and "HAVING COUNT(*) < 3", but I don't like to assume that there aren't duplicate description entries.
Also, I prefer to lay it out like that so it's very easy to change to get any custom matching you need. For example, say you needed ids that had Apex and Bill but NOT Sierra:
HAVING MAX(CASE WHEN Description = 'Apex' THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN Description = 'BILL' THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN Description = 'SIERRA' THEN 1 ELSE 0 END) = 0
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 13, 2023 at 10:56 pm
And this is why we want posters to always put the DDL for their tables where we can see them. After 30+ years of doing SQL and RDBMS, I would think people understand this is not an option, but a definition of a table! Oh, oh well, we still have id-iots and tibbles in these postings. It's even worse over on Quora; we have postings for people asking how many primary keys a table can have. 🙁
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply