Query Help
-
create table test (A1 int,A2 int)
insert into test (A1,A2) select 100,1
insert into test (A1,A2) select 100,4
insert into test (A1,A2) select 100,5
insert into test (A1,A2) select 100,2
insert into test (A1,A2) select 200,1
insert into test (A1,A2) select 300,4
insert into test (A1,A2) select 400,3
insert into test (A1,A2) select 500,1
insert into test (A1,A2) select 600,1
insert into test (A1,A2) select 600,4
insert into test (A1,A2) select 600,3
select * from test
I want a query to select A1 which has the A2 value 1 and 4.
The output should be.
A1
100
600
Since both has 1 and 4.
-
if i understood your problem well, you could use something like this
select t1.A1 from test t1 join test t2
on t1.A1 = t2.A1 and t1.A2 =1 and t2.A2 = 4
you can look up 'Using Self-Joins' in BOL for more info or ... ask
-
select distinct A1 from test a
where
exists (select A2 from test b where a.A1 = b.A1 and A2 = 1)
and
exists (select A2 from test b where a.A1 = b.A1 and A2 = 4)
...and your only reply is slàinte mhath
-
sure dragos_sv, I shouldn't be so hasty with my ideas 😀
self join is much better.
...and your only reply is slàinte mhath
-
select A1from test
group by A1
having count(distinct case when A2 in (1,4) then A2 end)=2
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
One way:
SELECT t.A1FROMtest t
JOIN test t2
ON t.A1 = t2.A1
WHERE t.A2 = 1
AND t2.A2 = 4
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
WITH CTE AS
(SELECT A1,A2, ROW_NUMBER() OVER (PARTITION BY A1 ORDER BY A1) AS ROWNUM
FROM TEST
WHERE A2 = 1 OR A2 = 4
)
SELECT A1
FROM CTE
WHERE ROWNUM = 2
This will work in the given scenario. Little modification and you can fix a major bug in this query.
Regards,
Ankit
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.” -
select *
from test MAIN
where A1 in
(select A1
from (select A1, A2
from test Y
where A2 in (1, 4) – add as many distinct A2 values you want to see for A1
group by A1, A2
) SUB1
group by A1
having count(*) = 2) - number here must match the number of values included in the above subquery
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply