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 O
N
> 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 O
N
> bp3.rating = bp4.rating
>                        WHERE      (bp3.rating <> '') AND (bp3.rating IS NO
T
> 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 O
N
> bp3.rating = bp4.rating
>                        WHERE      (bp3.rating <> '') AND (bp3.rating IS NO
T
> NULL)
> ORDER BY bp4.Cnt DESC) as t2 <----------------------------------
>
>
> AMB
>
>
> "Vlado Jasovic (excelleinc.com)" 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 a
ll
> 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:
> 

Thank you very much for quick and great answer.


"Alejandro Mesa" < AlejandroMesa@discus
sions.microsoft.com> wrote in message
news:D75847A7-61FC-4A41-BD91- 8F88C6FFC1B6@microso
ft.com...
> KH,
>
> I think he is using the "order by" to asure that each "select top ..."
> will
> be consistent.
>
>
> AMB
>
> "KH" wrote:
> 

Ach, indeed... missed that!


"Alejandro Mesa" wrote:

> KH,
>
> I think he is using the "order by" to asure that each "select top ..." wil
l
> be consistent.
>
>
> AMB
>
> "KH" wrote:
>