Need help with UNION statement

Hello,
I'm trying to join results from two tables and still don't understand why
the following statement doesn't work:
SELECT rating
FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
FROM businesspartners bp INNER JOIN
(SELECT rating,
COUNT(*) AS Cnt
FROM
businesspartners
GROUP BY rating) bp2 ON
bp.rating = bp2.rating
WHERE (bp.rating <> '') AND (bp.rating IS NOT
NULL)
ORDER BY bp2.Cnt DESC)
UNION
SELECT rating
FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
FROM businesspartners bp3 INNER JOIN
(SELECT rating,
COUNT(*) AS Cnt
FROM
businesspartners
GROUP BY rating) bp4 ON
bp3.rating = bp4.rating
WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
NULL)
ORDER BY bp4.Cnt DESC)
I tried each table and I get correct results, only when I tried to "union"
them it's giving me "Incorrect syntax near the keyword 'UNION'"Give an alias to each derived table.
SELECT rating
FROM
(
SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
FROM businesspartners bp INNER JOIN
(
SELECT rating, COUNT(*) AS Cnt
FROM businesspartners
GROUP BY rating
) bp2
ON bp.rating = bp2.rating
WHERE (bp.rating <> '') AND (bp.rating IS NOT NULL)
ORDER BY bp2.Cnt DESC
) as t1 <--
UNION
SELECT rating
FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
FROM businesspartners bp3 INNER JOIN
(SELECT rating,
COUNT(*) AS Cnt
FROM
businesspartners
GROUP BY rating) bp4 ON
bp3.rating = bp4.rating
WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
NULL)
ORDER BY bp4.Cnt DESC) as t2 <--
AMB
"Vlado Jasovic (excelleinc.com)" wrote:
> Hello,
> I'm trying to join results from two tables and still don't understand why
> the following statement doesn't work:
> SELECT rating
> FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> FROM businesspartners bp INNER JOIN
> (SELECT rating,
> COUNT(*) AS Cnt
> FROM
> businesspartners
> GROUP BY rating) bp2 ON
> bp.rating = bp2.rating
> WHERE (bp.rating <> '') AND (bp.rating IS NOT
> NULL)
> ORDER BY bp2.Cnt DESC)
> UNION
> SELECT rating
> FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> FROM businesspartners bp3 INNER JOIN
> (SELECT rating,
> COUNT(*) AS Cnt
> FROM
> businesspartners
> GROUP BY rating) bp4 ON
> bp3.rating = bp4.rating
> WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
> NULL)
> ORDER BY bp4.Cnt DESC)
> I tried each table and I get correct results, only when I tried to "union"
> them it's giving me "Incorrect syntax near the keyword 'UNION'"
>
>|||Also the whole query can only have one ORDER BY clause ... at the end of all
UNIONed sleect statements.
Also one note, you may or may not know the difference between "UNION" and
"UNION ALL" ... often people want "UNION ALL" and don't know it.
"Alejandro Mesa" wrote:
> Give an alias to each derived table.
> SELECT rating
> FROM
> (
> SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> FROM businesspartners bp INNER JOIN
> (
> SELECT rating, COUNT(*) AS Cnt
> FROM businesspartners
> GROUP BY rating
> ) bp2
> ON bp.rating = bp2.rating
> WHERE (bp.rating <> '') AND (bp.rating IS NOT NULL)
> ORDER BY bp2.Cnt DESC
> ) as t1 <--
> UNION
> SELECT rating
> FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> FROM businesspartners bp3 INNER JOIN
> (SELECT rating,
> COUNT(*) AS Cnt
> FROM
> businesspartners
> GROUP BY rating) bp4 ON
> bp3.rating = bp4.rating
> WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
> NULL)
> ORDER BY bp4.Cnt DESC) as t2 <--
>
> AMB
>
> "Vlado Jasovic (excelleinc.com)" wrote:
> > Hello,
> >
> > I'm trying to join results from two tables and still don't understand why
> > the following statement doesn't work:
> >
> > SELECT rating
> > FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> > FROM businesspartners bp INNER JOIN
> > (SELECT rating,
> > COUNT(*) AS Cnt
> > FROM
> > businesspartners
> > GROUP BY rating) bp2 ON
> > bp.rating = bp2.rating
> > WHERE (bp.rating <> '') AND (bp.rating IS NOT
> > NULL)
> > ORDER BY bp2.Cnt DESC)
> > UNION
> > SELECT rating
> > FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> > FROM businesspartners bp3 INNER JOIN
> > (SELECT rating,
> > COUNT(*) AS Cnt
> > FROM
> > businesspartners
> > GROUP BY rating) bp4 ON
> > bp3.rating = bp4.rating
> > WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
> > NULL)
> > ORDER BY bp4.Cnt DESC)
> >
> > I tried each table and I get correct results, only when I tried to "union"
> > them it's giving me "Incorrect syntax near the keyword 'UNION'"
> >
> >
> >|||KH,
I think he is using the "order by" to asure that each "select top ..." will
be consistent.
AMB
"KH" wrote:
> Also the whole query can only have one ORDER BY clause ... at the end of all
> UNIONed sleect statements.
> Also one note, you may or may not know the difference between "UNION" and
> "UNION ALL" ... often people want "UNION ALL" and don't know it.
>
> "Alejandro Mesa" wrote:
> > Give an alias to each derived table.
> >
> > SELECT rating
> > FROM
> > (
> > SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> > FROM businesspartners bp INNER JOIN
> > (
> > SELECT rating, COUNT(*) AS Cnt
> > FROM businesspartners
> > GROUP BY rating
> > ) bp2
> > ON bp.rating = bp2.rating
> > WHERE (bp.rating <> '') AND (bp.rating IS NOT NULL)
> > ORDER BY bp2.Cnt DESC
> > ) as t1 <--
> >
> > UNION
> >
> > SELECT rating
> > FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> > FROM businesspartners bp3 INNER JOIN
> > (SELECT rating,
> > COUNT(*) AS Cnt
> > FROM
> > businesspartners
> > GROUP BY rating) bp4 ON
> > bp3.rating = bp4.rating
> > WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
> > NULL)
> > ORDER BY bp4.Cnt DESC) as t2 <--
> >
> >
> > AMB
> >
> >
> > "Vlado Jasovic (excelleinc.com)" wrote:
> >
> > > Hello,
> > >
> > > I'm trying to join results from two tables and still don't understand why
> > > the following statement doesn't work:
> > >
> > > SELECT rating
> > > FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> > > FROM businesspartners bp INNER JOIN
> > > (SELECT rating,
> > > COUNT(*) AS Cnt
> > > FROM
> > > businesspartners
> > > GROUP BY rating) bp2 ON
> > > bp.rating = bp2.rating
> > > WHERE (bp.rating <> '') AND (bp.rating IS NOT
> > > NULL)
> > > ORDER BY bp2.Cnt DESC)
> > > UNION
> > > SELECT rating
> > > FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> > > FROM businesspartners bp3 INNER JOIN
> > > (SELECT rating,
> > > COUNT(*) AS Cnt
> > > FROM
> > > businesspartners
> > > GROUP BY rating) bp4 ON
> > > bp3.rating = bp4.rating
> > > WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
> > > NULL)
> > > ORDER BY bp4.Cnt DESC)
> > >
> > > I tried each table and I get correct results, only when I tried to "union"
> > > them it's giving me "Incorrect syntax near the keyword 'UNION'"
> > >
> > >
> > >|||Thank you very much for quick and great answer.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:D75847A7-61FC-4A41-BD91-8F88C6FFC1B6@.microsoft.com...
> KH,
> I think he is using the "order by" to asure that each "select top ..."
> will
> be consistent.
>
> AMB
> "KH" wrote:
>> Also the whole query can only have one ORDER BY clause ... at the end of
>> all
>> UNIONed sleect statements.
>> Also one note, you may or may not know the difference between "UNION" and
>> "UNION ALL" ... often people want "UNION ALL" and don't know it.
>>
>> "Alejandro Mesa" wrote:
>> > Give an alias to each derived table.
>> >
>> > SELECT rating
>> > FROM
>> > (
>> > SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
>> > FROM businesspartners bp INNER JOIN
>> > (
>> > SELECT rating, COUNT(*) AS Cnt
>> > FROM businesspartners
>> > GROUP BY rating
>> > ) bp2
>> > ON bp.rating = bp2.rating
>> > WHERE (bp.rating <> '') AND (bp.rating IS NOT NULL)
>> > ORDER BY bp2.Cnt DESC
>> > ) as t1 <--
>> >
>> > UNION
>> >
>> > SELECT rating
>> > FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
>> > FROM businesspartners bp3 INNER JOIN
>> > (SELECT rating,
>> > COUNT(*) AS Cnt
>> > FROM
>> > businesspartners
>> > GROUP BY rating)
>> > bp4 ON
>> > bp3.rating = bp4.rating
>> > WHERE (bp3.rating <> '') AND (bp3.rating IS
>> > NOT
>> > NULL)
>> > ORDER BY bp4.Cnt DESC) as t2 <--
>> >
>> >
>> > AMB
>> >
>> >
>> > "Vlado Jasovic (excelleinc.com)" wrote:
>> >
>> > > Hello,
>> > >
>> > > I'm trying to join results from two tables and still don't understand
>> > > why
>> > > the following statement doesn't work:
>> > >
>> > > SELECT rating
>> > > FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
>> > > FROM businesspartners bp INNER JOIN
>> > > (SELECT rating,
>> > > COUNT(*) AS Cnt
>> > > FROM
>> > > businesspartners
>> > > GROUP BY rating)
>> > > bp2 ON
>> > > bp.rating = bp2.rating
>> > > WHERE (bp.rating <> '') AND (bp.rating IS
>> > > NOT
>> > > NULL)
>> > > ORDER BY bp2.Cnt DESC)
>> > > UNION
>> > > SELECT rating
>> > > FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
>> > > FROM businesspartners bp3 INNER JOIN
>> > > (SELECT rating,
>> > > COUNT(*) AS Cnt
>> > > FROM
>> > > businesspartners
>> > > GROUP BY rating)
>> > > bp4 ON
>> > > bp3.rating = bp4.rating
>> > > WHERE (bp3.rating <> '') AND (bp3.rating
>> > > IS NOT
>> > > NULL)
>> > > ORDER BY bp4.Cnt DESC)
>> > >
>> > > I tried each table and I get correct results, only when I tried to
>> > > "union"
>> > > them it's giving me "Incorrect syntax near the keyword 'UNION'"
>> > >
>> > >
>> > >|||Ach, indeed... missed that!
"Alejandro Mesa" wrote:
> KH,
> I think he is using the "order by" to asure that each "select top ..." will
> be consistent.
>
> AMB
> "KH" wrote:
> > Also the whole query can only have one ORDER BY clause ... at the end of all
> > UNIONed sleect statements.
> >
> > Also one note, you may or may not know the difference between "UNION" and
> > "UNION ALL" ... often people want "UNION ALL" and don't know it.
> >
> >
> > "Alejandro Mesa" wrote:
> >
> > > Give an alias to each derived table.
> > >
> > > SELECT rating
> > > FROM
> > > (
> > > SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> > > FROM businesspartners bp INNER JOIN
> > > (
> > > SELECT rating, COUNT(*) AS Cnt
> > > FROM businesspartners
> > > GROUP BY rating
> > > ) bp2
> > > ON bp.rating = bp2.rating
> > > WHERE (bp.rating <> '') AND (bp.rating IS NOT NULL)
> > > ORDER BY bp2.Cnt DESC
> > > ) as t1 <--
> > >
> > > UNION
> > >
> > > SELECT rating
> > > FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> > > FROM businesspartners bp3 INNER JOIN
> > > (SELECT rating,
> > > COUNT(*) AS Cnt
> > > FROM
> > > businesspartners
> > > GROUP BY rating) bp4 ON
> > > bp3.rating = bp4.rating
> > > WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
> > > NULL)
> > > ORDER BY bp4.Cnt DESC) as t2 <--
> > >
> > >
> > > AMB
> > >
> > >
> > > "Vlado Jasovic (excelleinc.com)" wrote:
> > >
> > > > Hello,
> > > >
> > > > I'm trying to join results from two tables and still don't understand why
> > > > the following statement doesn't work:
> > > >
> > > > SELECT rating
> > > > FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> > > > FROM businesspartners bp INNER JOIN
> > > > (SELECT rating,
> > > > COUNT(*) AS Cnt
> > > > FROM
> > > > businesspartners
> > > > GROUP BY rating) bp2 ON
> > > > bp.rating = bp2.rating
> > > > WHERE (bp.rating <> '') AND (bp.rating IS NOT
> > > > NULL)
> > > > ORDER BY bp2.Cnt DESC)
> > > > UNION
> > > > SELECT rating
> > > > FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> > > > FROM businesspartners bp3 INNER JOIN
> > > > (SELECT rating,
> > > > COUNT(*) AS Cnt
> > > > FROM
> > > > businesspartners
> > > > GROUP BY rating) bp4 ON
> > > > bp3.rating = bp4.rating
> > > > WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
> > > > NULL)
> > > > ORDER BY bp4.Cnt DESC)
> > > >
> > > > I tried each table and I get correct results, only when I tried to "union"
> > > > them it's giving me "Incorrect syntax near the keyword 'UNION'"
> > > >
> > > >
> > > >