September 17, 2014 at 8:32 pm
I want to know if my value in a specific column exists in several rows in another table.
i.e. Table1 has a column called indexcolumn and it has 1 record with the values "cltstyle, InvNbr, PoNbr"
Table1:
indexColumn
cltstyle, InvNbr, PoNbr
Table2 has 2 columns (columnName, indexId). There are many records and I want to query the rows that have the 3 exact values above with the same indexid.
indexid, ColumnName
1, cltstyle
2, id
3, Tab9
4, cltstyle
4, InvNbr
4, PoNbr
5, InvNbr
6, ID
6, PoNbr
6, InvNbr
Results:
4, cltstyle
4, InvNbr
4, PoNbr
September 17, 2014 at 9:21 pm
Sounds like you could do this with a parameterized query (so a stored procedure) with a join between the two tables in question.
September 18, 2014 at 12:25 am
Can you give me an example of how to do this?
September 18, 2014 at 1:22 am
GrassHopper (9/18/2014)
Can you give me an example of how to do this?
Sure. Give us some data in a consumable format and you'll get the answer right away.
Read the first article linked in my signature line to find out how to post sample data in a consumable format.
-- Gianluca Sartori
September 18, 2014 at 2:03 am
Here is a simple solution which uses charindex for the matching and a combination of len and replace to count the expected number of matches, should be enough to get you passed this hurdle.
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @Table1 TABLE(indexColumn VARCHAR(50));
INSERT INTO @Table1(indexColumn) VALUES ('cltstyle, InvNbr, PoNbr');
DECLARE @Table2 TABLE (indexid INT NOT NULL, ColumnName VARCHAR(50) NOT NULL)
INSERT INTO @Table2 (indexid,ColumnName)
VALUES
(1,'cltstyle')
,(2,'id')
,(3,'Tab9')
,(4,'cltstyle')
,(4,'InvNbr')
,(4,'PoNbr')
,(5,'InvNbr')
,(6,'ID')
,(6,'PoNbr')
,(6,'InvNbr');
/*
Results:
4, cltstyle
4, InvNbr
4, PoNbr
*/
;WITH BASE_DATA AS
(
SELECT
T2.indexid
,T2.ColumnName
,LEN(T1.indexColumn + CHAR(124)) - LEN(REPLACE(T1.indexColumn + CHAR(124),CHAR(44),'')) + 1 AS COL_COUNT
,COUNT(T1.indexColumn) OVER
(
PARTITION BY T2.indexid
) AS MATCH_COUNT
FROM @Table2 T2
OUTER APPLY @Table1 T1
WHERE CHARINDEX(T2.ColumnName,T1.indexColumn) > 0
)
SELECT
BD.indexid
,BD.ColumnName
FROM BASE_DATA BD
WHERE BD.COL_COUNT = BD.MATCH_COUNT;
Results
indexid ColumnName
----------- -----------
4 cltstyle
4 InvNbr
4 PoNbr
September 18, 2014 at 9:13 am
Thanks, it was very close and useful. I ended up with this :
WITH DtlIdxcolumn (tblnm,type_desc,object_id,index_id,column_id,name)
AS
-- Define the CTE query.
(
select main.name as tblnm,main.type_desc,Sec.*
from
(
select Obj.name,obj.object_id,Idx.index_id,Idx.type_desc
from sys.indexes as Idx
inner join
sys.objects as Obj
on Idx.object_id=Obj.Object_id
) as Main
inner join
(
select idxcol.object_id,idxcol.index_id,col.column_id,col.name
from sys.index_columns as Idxcol
inner join
Sys.columns as col
on Idxcol.object_id=col.object_id and Idxcol.column_id=col.column_id
) as Sec
on (Main.object_id=Sec.object_id) and (main.index_id=Sec.index_id)
)
Select F1.*,F2.object_id,F2.tblnm ,(case when (F2.Retievdcolumnnm) IS null then 'NOTEXISTS' else 'EXISTS' end)comments
From
(
SELECT A.*,B.object_id,B.name
FROM [PRGX_AS_DEV_SandBox_REFERENCE_TABLES].[dbo].[RequiredObjects] as A
inner join
[PRGX_AS_CC_SandBox_Pricing_2014].[sys].[objects] as B
on A.IndexTable=B.name
where ObjectType='index'
)as F1
Left join
(
Select distinct ST2.object_id,ST2.tblnm,
substring(
(
Select ', '+ convert(varchar(500),rtrim(ltrim(name))) AS [text()]
From DtlIdxcolumn as ST1
Where (ST1.object_id = ST2.object_id) and (st1.index_id=st2.index_id)
ORDER BY ST1.name
For XML PATH ('')), 2, 1000
) [Retievdcolumnnm]
From DtlIdxcolumn ST2
)as F2
on (F1.object_id=F2.object_id) and (rtrim(ltrim(upper(F1.IndexColumn)))=rtrim(ltrim((F2.Retievdcolumnnm))))
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply