Select Query

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.

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

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

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.

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

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.