Where clause question?

Question

Experts,

CREATE TABLE #TEMPA
(
	ID	INT,
	ST	VARCHAR(50)
)

CREATE TABLE #TEMPB
(
	ID	INT,
	ST	VARCHAR(50)
)

INSERT INTO #TEMPA VALUES ( 1, '01091,01092' )
INSERT INTO #TEMPA VALUES ( 2, '01091,01092,01093' )
INSERT INTO #TEMPA VALUES ( 3, '01092,01093' )

INSERT INTO #TEMPB VALUES ( 1, '01091' )
INSERT INTO #TEMPB VALUES ( 1, '01092' )
INSERT INTO #TEMPB VALUES ( 2, '01091' )
INSERT INTO #TEMPB VALUES ( 2, '01093' )
INSERT INTO #TEMPB VALUES ( 2, '01094' )
INSERT INTO #TEMPB VALUES ( 3, '01092' )
INSERT INTO #TEMPB VALUES ( 3, '01093' )
INSERT INTO #TEMPB VALUES ( 3, '01094' )

--SELECT	DISTINCT a.*
--FROM	#TEMPA a INNER JOIN #TEMPB b ON a.ID = b.ID AND CAST(b.ST AS VARCHAR(50)) LIKE '''%'+CAST(a.ST AS VARCHAR(50))+'%'''

--SELECT	ID, @S = ST
--FROM	#TEMPA

--SELECt	@Split = ','

--SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')

--SELECT	[Value] = T.c.value('.','varchar(20)')
--FROM	@X.nodes('/root/s') T(c)



;WITH cte AS
(
	SELECT T.ID, T.ST, F.Value, MAX(F.ID) OVER (PARTITION BY T.Id) AS [cntVals]
	FROM #TEMPA T CROSS APPLY dbo.fnSplit(T.ST, ', ') F
)

--SELECT	*
--FROM	cte

SELECT	cte.Id, cte.st, cte.cntVals
FROM	cte INNER JOIN #TEMPB b ON cte.ID = b.ID AND cte.VALUE = b.ST
GROUP BY cte.ID, cte.ST, cte.cntVals
HAVING COUNT(cte.Id) = cte.cntVals

DROP TABLE #TEMPA
DROP TABLE #TEMPB

My expected result is:-

SELECT '1' AS 'ID', '01091,01092' AS 'ST'

As #TEMPB has extra value I want to get rid of ID=3.

Any help please?

Thanks

Kumar

---

Please do let us know your feedback. Thank You - KG, MCTS

Answer 1

Dear Kumar,

Do you want this?

WHERE cte.Id = 1

If you don't, please explain more.

Regards

Saeid

---

http://sqldevelop.wordpress.com/

Answer 2

Hi,

You have hard coded WHERE clause with cte.Id = 1, I was looking something more dynamic

Thanks

Kumar

---

Please do let us know your feedback. Thank You - KG, MCTS

Answer 3

Contrary to popular belief there is such a thing as a stupid question.

This is a good example of a stupid question because it is not possible to answer it effectively without writing an essay as the answer.

Please ask a good question.  Not a stupid question.

So.  Now your question is?  I would like to make cte.Id=1 more dynamic?  You mean like use a stored procedure?  Like using a variable with a programming loop?

Don't give us your whole code to fix...you will get better responses by giving a smaller example of code you want to modify and what you want to do.

Answer 4

Guess you need compare sets of A.ST (comma separated) and  {B.ST} where a.id=b.id Try modify your query as follows

CREATE TABLE #TEMPA
(
	ID	INT,
	ST	VARCHAR(50)
)

CREATE TABLE #TEMPB
(
	ID	INT,
	ST	VARCHAR(50)
)

INSERT INTO #TEMPA VALUES ( 1, '01091,01092' )
INSERT INTO #TEMPA VALUES ( 2, '01091,01092,01093' )
INSERT INTO #TEMPA VALUES ( 3, '01092,01093' )

INSERT INTO #TEMPB VALUES ( 1, '01091' )
INSERT INTO #TEMPB VALUES ( 1, '01092' )
INSERT INTO #TEMPB VALUES ( 2, '01091' )
INSERT INTO #TEMPB VALUES ( 2, '01093' )
INSERT INTO #TEMPB VALUES ( 2, '01094' )
INSERT INTO #TEMPB VALUES ( 3, '01092' )
INSERT INTO #TEMPB VALUES ( 3, '01093' )
INSERT INTO #TEMPB VALUES ( 3, '01094' )


;WITH cte AS
(
	SELECT T.ID, T.ST, F.Element, MAX(F.ID) OVER (PARTITION BY T.Id) AS [cntVals]
	FROM #TEMPA T CROSS APPLY dbo.Split(T.ST, ',') F
)

SELECT	cte.Id, cte.st, cte.cntVals
FROM cte
JOIN ( select id, st, count(*)over (partition by id) n from  #TEMPB) b
 -- the same number of values
 ON cte.ID = b.ID AND cte.Element = b.ST and b.n = cte.cntVals
GROUP BY cte.ID, cte.ST, cte.cntVals
-- and all the same values
HAVING COUNT(*) = cte.cntVals

Note my split function is a bit different:  other function  name and returns  table (Id, Element).

---

Serg

Answer 5

If it's the set compare problem try full join version

SELECT T.ID, T.ST
FROM #TEMPA T 
WHERE not exists (select 1
	from  dbo.Split(T.ST, ',') F
	full join (select ST from #TEMPB b where b.id =t.id) G
	 on F.Element = g.ST
	where f.Element is null or g.ST is null)

---

Serg