Hi, My table structure is like this: declare @t1 table(col1 int,col2 int) INSERT @t1 SELECT 1,3 UNION ALL SELECT 1,4 UNION ALL SELECT 2,4 UNION ALL SELECT 3,3 UNION ALL SELECT 3,4 UNION ALL SELECT 3,5 UNION ALL SELECT 4,3 UNION ALL SELECT 4,6 My query is, i need list of all the col1 where col1 is only assigned with col2 values 3 and 4. In the above case it should return col1 1 I tried using INTERSECT but looking for a better query How do i write this more effectively.
Couple of solutions: SELECT DISTINCT t1.col1 FROM @t1 t1 INNER JOIN @t1 t2 ON t1.col1 = t2.col1 WHERE t1.col1 = 3 and t2.col1 = 4 or SELECT @t1.col1 FROM @t1 WHERE @t1.col1 = 3 AND @t1.col1 IN (SELECT t2.col1 FROM @t1 t2 WHERE t2.col1 = 4) or SELECT t1.col1 FROM @t1 t1 WHERE t1.col1 = 3 AND EXISTS (SELECT t2.col1 FROM @t1 t2 WHERE t2.col1 = 4 AND t2.col1 = t1.col1)
Hi Adriaan, I tried all your approaches but no result is being returned. Also the values which i'm going to check will be dynamic and comma separated.
Will this work? SELECT col1 FROM @t1 WHERE col2 IN (3, 4) GROUP BY col1 HAVING COUNT(*) = 2 -- Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:http://www.insidesql.de
quote:Originally posted by FrankKalis Will this work? SELECT col1 FROM @t1 WHERE col2 IN (3, 4) GROUP BY col1 HAVING COUNT(*) = 2 -- Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:http://www.insidesql.de I had already tried this but it returns two values i.e. col1 1 3 my desired output is col1 1
Aah, I see, I've misinterpreted "only assigned with col2 values 3 and 4." in our question. <br />Will go home now and think about it. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
Sorry for the error: the criteria for column value 3 or 4 must be for col2 (not col1). SELECT DISTINCT t1.col1 FROM @t1 t1 INNER JOIN @t1 t2 ON t1.col1 = t2.col1 WHERE t1.col2 = 3 and t2.col2 = 4 or SELECT @t1.col1 FROM @t1 WHERE @t1.col2 = 3 AND @t1.col1 IN (SELECT t2.col1 FROM @t1 t2 WHERE t2.col2 = 4) or SELECT t1.col1 FROM @t1 t1 WHERE t1.col2 = 3 AND EXISTS (SELECT t2.col1 FROM @t1 t2 WHERE t2.col2 = 4 AND t2.col1 = t1.col1)
Ranitjain - you are close to 1500 postings, and you don't know how to find out small errors like that?
Hi Adriaan, I had already mentioned, i have got the result using intersect but was not happy with it. I thought there might be some better way. here is my way: select col1,count(*) from @t1 where col2 in (3,4) group by col1 intersect select col1,count(*) from @t1 group by col1 having count(col2) = 2 Also, if i consider your approaches, it will be quite difficult because the criteria i.e.(3,4) will be dynamic and comma separated, and sometimes can be only 4 or (4,2,3.....) In this case i can not build so many correlated queries. Also i feel everyone should have approach as looking for better always irrespective of post count, one should be hungry for knowledge, What do you say? I also feel, the more you are approaching towards 5000 mark, you are becoming more harsh
quote: select col1,count(*) from @t1 where col2 in (3,4) group by col1 intersect select col1,count(*) from @t1 group by col1 having count(col2) = 2 Are you using SQL Server 2005? Also, in the above code, how do you replace (3,4) by your csv? Madhivanan Failing to plan is Planning to fail
Hi Madhivanan, Yes, it true that this query i ran on 2005, but was looking for the one which will run on both using some other approach. Anyways i modified in this way declare @locs table(locid int) INSERT @locs SELECT aaiLocationID from dbo.[fn_LocHierarchy](@aaiLocationID) SELECT tmp.aaiProfileID FROM (SELECT aaiProfileID,COUNT(*) c FROM aaiprofiletolocation WHERE aaiLocationID IN (SELECT locid FROM @locs) GROUP BY aaiProfileID)tmp INNER JOIN (SELECT aaiProfileID,COUNT(*) c FROM aaiprofiletolocation GROUP BY aaiProfileID HAVING COUNT(*) = (SELECT COUNT(*) FROM @locs))tmp1 ON tmp.aaiProfileID=tmp1.aaiProfileID and tmp.c=tmp1.c here, @aaiLocationID will return me all the csv child locations
Why the derived tables? declare @locs table(locid int) DECLARE @ROWS INT INSERT @locs SELECT aaiLocationID from dbo.[fn_LocHierarchy](@aaiLocationID) SET @ROWS = @@ROWCOUNT SELECT aaiProfileID,COUNT(*) c FROM aaiprofiletolocation GROUP BY aaiProfileID HAVING COUNT(*) = @rows Don't know what the fn_LocHierarchy function does - but probably no reason why it couldn't be done locally in this procedure.
Hi Adriaan, Thanks for this catch, yes i could do this without doing count(*) again on derived table. I missed it completely. really good catch. Now you can see, how things becomes easier after discussion even for smaller queries like this. BTW, fn_lochierarchy function return me comma separated list of all the childs of @aaiLocationID parameter value
And you are probably looking up those child aaiLocationId values in another column, or another table? The solution seems to be over-engineered, where you should just have worked out the basic query that joins the relevant tables. Don't use UDFs just because they appeal to the programmer in you! Also note that the query returns all aaiProfileID values that happen have the same number of entries in the aaiProfileToLocation table, as has the @aaiLocationId parameter value. You probably have to add the @aaiLocationId parameter to the shorter query - but only you know the logic behind the query. (You may not see a problem with the current data, but it may come up later when there is more varied data in the aaiProfileToLocation table.)
Hi Adriaan, i agree on what you are saying. Well the function not just returns child's of a single location but it also returns child's of those childs which means the complete list of locations which appears in the hierarchy. In my code i want to return not just single profileid but all the profileid's which has been assigned only 3,4 locations or may be some other list. The idea is if a profile has been assigned with only those locations than that parent location can not be dropped whereas if a profile has also got childs/parents of some other locations then it can be dropped. So i'm intersecting two queries. Anyways, i feel this should work and thanx for all your support and contribution.
You could also have the function return the number of rows matching the criteria. Anyway, your filtering is still based on the assumption that any @aaiLocationId parameter you choose will have a unique number of matches, among all possible @aaiLocationId values and their matches. This is not really life-like.
My general approach(not sure about performance) Declare @t table(c1 char(1), c2 int) Declare @c2list varchar(10), @len int Insert into @t Select 'a',3 union all Select 'a',4 union all Select 'b',3 union all Select 'b',4 union all Select 'b',5 union all Select 'c',2 union all Select 'c',3 --If values are static then Select t1.c1 from @t t1 inner join (select c1 from @t group by c1 having count(*)=2 )t2 on t1.c1=t2.c1 where t1.c2 in (3,4) group by t1.c1 having count(*)=2 --If values are dynamic then set @c2list='3,4' Set @len=len(@c2list)-len(replace(@c2list,',',''))+1 Select t1.c1,@c2list as value from @t t1 inner join (select c1 from @t group by c1 having count(*)=@len )t2 on t1.c1=t2.c1 where '%,'+@c2list+',%' like '%,'+cast(t1.c2 as varchar(10))+',%' group by t1.c1 having count(*)=@len set @c2list='3,4,5' Select t1.c1,@c2list as value from @t t1 inner join (select c1 from @t group by c1 having count(*)=@len )t2 on t1.c1=t2.c1 where '%,'+@c2list+',%' like '%,'+cast(t1.c2 as varchar(10))+',%' group by t1.c1 having count(*)=@len Madhivanan Failing to plan is Planning to fail
I dont know why new lines are not working with Code tag Madhivanan Failing to plan is Planning to fail
Hi Madhivanan, I tried your approach and it's working the same way i desired. The execution plan is same as of code which i had posted and i feel my approach of using intersect i.e. INNER JOIN is very much similar to what you have posted. Still i will go with this as it looks much simpler. Select t1.c1 from @t t1 inner join (select c1 from @t group by c1having count(*)=2)t2 on t1.c1=t2.c1 where t1.c2 in (3,4) group by t1.c1 having count(*)=2 Thanks alot for your support
If you use intersect compare it with this solution and see the performance. I didnt use SQL Server 2005 Madhivanan Failing to plan is Planning to fail
Hi Madhivanan, This solution and Using INTERSECT solution shows the same execution plan and have almost no difference in performance. At the moment i have very less data, but will test it afterwards under load as well.
Hi Ranjit, is it what you are looking for: declare @t1 table(col1 int,col2 int) INSERT @t1 SELECT 1,3 UNION ALL SELECT 1,4 UNION ALL SELECT 2,4 UNION ALL SELECT 3,3 UNION ALL SELECT 3,4 UNION ALL SELECT 3,5 UNION ALL SELECT 4,3 UNION ALL SELECT 4,6 select a.col1 from @t1 a join @t1 b on a.col1 = b.col1 where a.col2 = 3 and b.col2 = 4 and not exists(select * from @t1 c where c.col1 = a.col1 and c.col2 not in (3,4) ) It also can be done using left join instead of exists.