need output where same column has more than one pirce of data

CREATE TABLE test (material int, col1 varchar(3), col2 varchar(1))
INSERT INTO test values(111,'a12','a')
INSERT INTO test values(111,'d33','a')
INSERT INTO test values(222,'a25','a')
INSERT INTO test values(222,'g21','e')
INSERT INTO test values(333,'a65','a')
INSERT INTO test values(333,'a64','e')
INSERT INTO test values(444,'w11','f')
INSERT INTO test values(555,'a41','a')
INSERT INTO test values(555,'r99','a')
INSERT INTO test values(555,'a76','e')
I need to output the records where the same material has an 'A' as the first
character in col1 and col2 has both an 'A' and an 'E'. So the output should
look like this:
Material col1 col2
333 a65 a
333 a64 e
555 a41 a
555 a76 e
TIAWithout better specs, my first guess is:
SELECT material, MIN(col1), col2
FROM test
GROUP BY material, col2
ORDER BY material, col2
Now, you say you want the output to be those 4 rows. Are those the ONLY
rows that should be returned? If so, why are you eliminating the rows where
material = 111, for example?
Please see http://www.aspfaq.com/5006 for some information on providing
requirements that make follow-up questions, and a delayed solution, much
less likely...
A
"Chesster" <Chesster@.discussions.microsoft.com> wrote in message
news:1DA8479F-3093-40D0-B63D-C2B7630D0DA8@.microsoft.com...
> CREATE TABLE test (material int, col1 varchar(3), col2 varchar(1))
> INSERT INTO test values(111,'a12','a')
> INSERT INTO test values(111,'d33','a')
> INSERT INTO test values(222,'a25','a')
> INSERT INTO test values(222,'g21','e')
> INSERT INTO test values(333,'a65','a')
> INSERT INTO test values(333,'a64','e')
> INSERT INTO test values(444,'w11','f')
> INSERT INTO test values(555,'a41','a')
> INSERT INTO test values(555,'r99','a')
> INSERT INTO test values(555,'a76','e')
> I need to output the records where the same material has an 'A' as the
> first
> character in col1 and col2 has both an 'A' and an 'E'. So the output
> should
> look like this:
> Material col1 col2
> 333 a65 a
> 333 a64 e
> 555 a41 a
> 555 a76 e
> TIA
>|||Yes, those are the only rows that should be returned.
Rows 111 should not be in the output because col2 does not have both an 'A'
and an 'E'.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Without better specs, my first guess is:
> SELECT material, MIN(col1), col2
> FROM test
> GROUP BY material, col2
> ORDER BY material, col2
> Now, you say you want the output to be those 4 rows. Are those the ONLY
> rows that should be returned? If so, why are you eliminating the rows whe
re
> material = 111, for example?
> Please see http://www.aspfaq.com/5006 for some information on providing
> requirements that make follow-up questions, and a delayed solution, much
> less likely...
> A
>
> "Chesster" <Chesster@.discussions.microsoft.com> wrote in message
> news:1DA8479F-3093-40D0-B63D-C2B7630D0DA8@.microsoft.com...
>
>|||SET NOCOUNT ON;
GO
CREATE TABLE test
(
material INT,
col1 VARCHAR(3),
col2 VARCHAR(1)
);
GO
INSERT test SELECT 111,'a12','a';
INSERT test SELECT 111,'d33','a';
INSERT test SELECT 222,'a25','a';
INSERT test SELECT 222,'g21','e';
INSERT test SELECT 333,'a65','a';
INSERT test SELECT 333,'a64','e';
INSERT test SELECT 444,'w11','f';
INSERT test SELECT 555,'a41','a';
INSERT test SELECT 555,'r99','a';
INSERT test SELECT 555,'a76','e';
SELECT t.material, MIN(t.col1), t.col2
FROM test t
WHERE
LEFT(t.col1,1) = 'a'
AND
(
(col2 = 'a' AND EXISTS
(
SELECT 1 FROM test tA
WHERE tA.material = t.material
AND LEFT(tA.col1,1) = 'a'
AND col2 = 'e'
))
OR
(col2 = 'e' AND EXISTS
(
SELECT 1 FROM test tB
WHERE tB.material = t.material
AND LEFT(tB.col1,1) = 'a'
AND col2 = 'a'
))
)
GROUP BY material, col2
ORDER BY material, col2;
GO
DROP TABLE test;
GO
"Chesster" <Chesster@.discussions.microsoft.com> wrote in message
news:854D3331-2645-4F01-8204-86AABF15AD91@.microsoft.com...
> Yes, those are the only rows that should be returned.
> Rows 111 should not be in the output because col2 does not have both an
> 'A'
> and an 'E'.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||select * from #test where [material] in(
select ta.[material] from (
select [material] from #test where col1 like 'a%' and col2='a') ta
join (
select [material] from #test where col1 like 'a%' and col2='e') te
on ta.[material] = te.[material]
)
and col2 in('a','e')
material col1 col2
-- -- --
333 a65 a
333 a64 e
555 a41 a
555 r99 a
555 a76 e
I see that
555 r99 a
is not present in your sample output. I don't see why it does not
belong in the result set|||> I see that
> 555 r99 a
> is not present in your sample output. I don't see why it does not
> belong in the result set
Because the first letter of col1 != 'a'|||thanks Aaron.
select * from #test t1
where col1 like 'a%'
and col2 in('a','e')
and (select count(*) from #test t2
where t1.[material] = t2.[material]
and t2.col1 like 'a%'
and t2.col2 in('a','e') and t2.col1<>t1.col1)>0
material col1 col2
-- -- --
333 a65 a
333 a64 e
555 a41 a
555 a76 e
(4 row(s) affected)|||select * from #test where [material] in(
select ta.[material] from (
select [material] from #test where col1 like 'a%' and col2='a') ta
join (
select [material] from #test where col1 like 'a%' and col2='e') te
on ta.[material] = te.[material]
)
and col2 in('a','e') and col1 like 'a%'
just added col1 like 'a%' at the end.
Regards