Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Where clause question?

Answered Where clause question?

  • Sunday, January 20, 2013 3:45 AM
     
      Has Code
    Sign In to Vote
    0

    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

     

All Replies

  • Sunday, January 20, 2013 6:52 AM
     
      Has Code
    Sign In to Vote
    0

    Dear Kumar,

    Do you want this?

    WHERE cte.Id = 1

    If you don't, please explain more.

    Regards

    Saeid

    http://sqldevelop.wordpress.com/

     
  • Sunday, January 20, 2013 3:15 PM
     
     
    Sign In to Vote
    0

    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

     
  • Sunday, January 20, 2013 3:49 PM
     
     
    Sign In to Vote
    0

    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.

    • Edited by CountryStyle Sunday, January 20, 2013 3:53 PM
    •  
     
  • Sunday, January 20, 2013 4:13 PM
     
     Answered Has Code
    Sign In to Vote
    0

    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


    • Edited by SergNL Sunday, January 20, 2013 4:15 PM
    • Marked As Answer by _Kumar Sunday, January 20, 2013 5:25 PM
    •  
     
  • Sunday, January 20, 2013 4:33 PM
     
      Has Code
    Sign In to Vote
    0

    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