Sub Query Exist

24 Aug 2006 10:17 PM

Skip

Hello All,
I am having trouble getting the following subquery to work. When it
runs I get the following error -

Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS

select fastener_name,program_name from #QA_Fastener_Check
where fastener_name in (SELECT fastener_name,COUNT(*) AS Dup

>From #QA_Fastener_Check GROUP BY fastener_name HAVING COUNT(*) > 1)

Thanks

24 Aug 2006 10:30 PM

Hugo Kornelis

On 24 Aug 2006 15:17:53 -0700, Skip wrote:

>Hello All,
>I am having trouble getting the following subquery to work. When it
>runs I get the following error -
>
> Only one expression can be specified in the select list when the
>subquery is not introduced with EXISTS
>
>select fastener_name,program_name from #QA_Fastener_Check
>where fastener_name in (SELECT fastener_name,COUNT(*) AS Dup
>>From #QA_Fastener_Check GROUP BY fastener_name HAVING COUNT(*) > 1)

Hi Skip,

Try:

SELECT fastener_name, program_name
FROM   #QA_Fastener_Check
WHERE fastener_name IN (SELECT   fastener_name
                         FROM     #QA_Fastener_Check
                         GROUP BY fastener_name
                         HAVING   COUNT(*) > 1);

--
Hugo Kornelis, SQL Server MVP

25 Aug 2006 12:30 AM

Skip

Yeah I guess COUNT(*) AS Dup would be a duplication in this case. I
will try tommorrow.
Thanks

Hugo Kornelis wrote:
Show quote

> On 24 Aug 2006 15:17:53 -0700, Skip wrote:
>
> >Hello All,
> >I am having trouble getting the following subquery to work. When it
> >runs I get the following error -
> >
> > Only one expression can be specified in the select list when the
> >subquery is not introduced with EXISTS
> >
> >select fastener_name,program_name from #QA_Fastener_Check
> >where fastener_name in (SELECT fastener_name,COUNT(*) AS Dup
> >>From #QA_Fastener_Check GROUP BY fastener_name HAVING COUNT(*) > 1)
>
> Hi Skip,
>
> Try:
>
> SELECT fastener_name, program_name
> FROM   #QA_Fastener_Check
> WHERE fastener_name IN (SELECT   fastener_name
>                          FROM     #QA_Fastener_Check
>                          GROUP BY fastener_name
>                          HAVING   COUNT(*) > 1);
>
> --
> Hugo Kornelis, SQL Server MVP

25 Aug 2006 12:57 PM

Skip

Worked great Thanks.

Skip wrote:
Show quote

> Yeah I guess COUNT(*) AS Dup would be a duplication in this case. I
> will try tommorrow.
> Thanks
>
>
> Hugo Kornelis wrote:
> > On 24 Aug 2006 15:17:53 -0700, Skip wrote:
> >
> > >Hello All,
> > >I am having trouble getting the following subquery to work. When it
> > >runs I get the following error -
> > >
> > > Only one expression can be specified in the select list when the
> > >subquery is not introduced with EXISTS
> > >
> > >select fastener_name,program_name from #QA_Fastener_Check
> > >where fastener_name in (SELECT fastener_name,COUNT(*) AS Dup
> > >>From #QA_Fastener_Check GROUP BY fastener_name HAVING COUNT(*) > 1)
> >
> > Hi Skip,
> >
> > Try:
> >
> > SELECT fastener_name, program_name
> > FROM   #QA_Fastener_Check
> > WHERE fastener_name IN (SELECT   fastener_name
> >                          FROM     #QA_Fastener_Check
> >                          GROUP BY fastener_name
> >                          HAVING   COUNT(*) > 1);
> >
> > --
> > Hugo Kornelis, SQL Server MVP