March 20, 2015 at 2:17 pm
I have a table with the structure below. I'm looking to get all records where the SAME record in eX_ID match all the criteria in category ID.
Example: Category_ID = 203 and 204. Would return 'BAR105842'
ex_IDcategory_IDID
BAR1058422032
BAR1058422043
BAR10584221033
BAR10584223089
BAR105842234133
FLE10311260736
FLE10311261737
FLE103112681775
FLE103112821776
FLE103112982755
XGEN14433153041
XGEN14433436267
XGEN14433426268
XGEN14433456269
XGEN14433416270
XGEN144338013168
XGEN144338313169
XGEN144341219312
Thanks!!
March 20, 2015 at 2:45 pm
Hi and welcome to the forum.
Not certain what you are after but this is my first interpretation
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_SAMPLE_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA;
CREATE TABLE dbo.TBL_SAMPLE_DATA
(
ex_ID VARCHAR(12) NOT NULL
,category_ID INT NOT NULL
,ID INT NOT NULL
)
INSERT INTO dbo.TBL_SAMPLE_DATA (ex_ID,category_ID,ID)
VALUES
('BAR105842',203,2)
,('BAR105842',204,3)
,('BAR105842',210,33)
,('BAR105842',230,89)
,('BAR105842',234,133)
,('FLE10311' ,260,736)
,('FLE10311' ,261,737)
,('FLE10311' ,268,1775)
,('FLE10311' ,282,1776)
,('FLE10311' ,298,2755)
,('XGEN1443' ,315,3041)
,('XGEN1443' ,343,6267)
,('XGEN1443' ,342,6268)
,('XGEN1443' ,345,6269)
,('XGEN1443' ,341,6270)
,('XGEN1443' ,380,13168)
,('XGEN1443' ,383,13169)
,('XGEN1443' ,412,19312);
DECLARE @category_ID INT = 204;
SELECT
SD.ex_ID
,SD.category_ID
,SD.ID
FROM dbo.TBL_SAMPLE_DATA SD
WHERE SD.ex_ID =
( SELECT ex_ID
FROM dbo.TBL_SAMPLE_DATA
WHERE category_ID = @category_ID
)
;
Results
ex_ID category_ID ID
------------ ----------- ----
BAR105842 203 2
BAR105842 204 3
BAR105842 210 33
BAR105842 230 89
BAR105842 234 133
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply