Query Help.

14 Jul 2006 9:02 AM

AJ

Hi all,

I have this monster query (at least i think it is).

SELECT
    c.ID, c.Company_Name, p.[level], 1 As QueryNbr
FROM
    (Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID)
LEFT JOIN
    Package AS p ON s.Package_ID = p.ID
WHERE
    c.Category = 'EXH'
AND (s.ID = (SELECT Max(ID) FROM Sale WHERE Company_ID = c.ID) Or
IsNull(s.ID))
AND EXISTS(SELECT Company_ID FROM Event_Company_Link WHERE Event_ID = 20 AND
Company_ID = c.ID)
AND (INT(Start_Date) <= 38911) AND (INT(End_Date) >= 38911)
AND Company_Name LIKE "% Inc% "
ORDER BY p.[level] DESC , c.Company_Name, c.ID
UNION
    SELECT
        c.ID, c.Company_Name, p.[level], 2 As QueryNbr
    FROM (Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID)
        LEFT JOIN Package AS p ON s.Package_ID = p.ID
    WHERE c.Category = 'EXH'
    AND (s.ID = (SELECT Max(ID) FROM Sale WHERE Company_ID = c.ID) Or
IsNull(s.ID))
    AND EXISTS(SELECT Company_ID FROM Event_Company_Link WHERE Event_ID = 20
AND Company_ID = c.ID)
    AND Company_Name LIKE "%Inc%"
    ORDER BY c.Company_Name, c.ID

My issue is, i need it to return distinct companies only; In this case c.ID
is the column i require to be unique.

The UNION clause normally filters out duplicates, but because there is an
extra column needed ('QueryNbr') to indicate which query the results were
retrieve in (1 or 2) this filtering isn't having an effect.

The UNION appears to look for duplicates on a row by row basic rather than a
single column;

Has anyone got any ideas on how to get around this issue?

I am suffering with Access in this problem!!!

Cheers,
Adam

14 Jul 2006 2:03 PM

Cowboy (Gregory A. Beamer) - MVP

Why do you have to know which query the results come from? Is that germaine
to your application? If so, which query should you choose if both queries
have the same result?

If you have to know that result, do this:

If query 1 wins (i.e., the results should say query 1 if found in both)
1. Create a temp table
2. Put results of query one in the table
3. Join query two to the results table and only find records that do not
appear in the temp table, insert those records
4. Query the temp table
5. After you have results, destroy the temp table

If query 2 wins, reverse the order in filling the temp table so query one
does not enter data entered by query 2.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

Show quote

"AJ" wrote:

> Hi all,
>
> I have this monster query (at least i think it is).
>
> SELECT
>     c.ID, c.Company_Name, p.[level], 1 As QueryNbr
> FROM
>     (Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID)
> LEFT JOIN
>     Package AS p ON s.Package_ID = p.ID
> WHERE
>     c.Category = 'EXH'
> AND (s.ID = (SELECT Max(ID) FROM Sale WHERE Company_ID = c.ID) Or
> IsNull(s.ID))
> AND EXISTS(SELECT Company_ID FROM Event_Company_Link WHERE Event_ID = 20 AND
> Company_ID = c.ID)
> AND (INT(Start_Date) <= 38911) AND (INT(End_Date) >= 38911)
> AND Company_Name LIKE "% Inc% "
> ORDER BY p.[level] DESC , c.Company_Name, c.ID
> UNION
>     SELECT
>         c.ID, c.Company_Name, p.[level], 2 As QueryNbr
>     FROM (Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID)
>         LEFT JOIN Package AS p ON s.Package_ID = p.ID
>     WHERE c.Category = 'EXH'
>     AND (s.ID = (SELECT Max(ID) FROM Sale WHERE Company_ID = c.ID) Or
> IsNull(s.ID))
>     AND EXISTS(SELECT Company_ID FROM Event_Company_Link WHERE Event_ID = 20
> AND Company_ID = c.ID)
>     AND Company_Name LIKE "%Inc%"
>     ORDER BY c.Company_Name, c.ID
>
> My issue is, i need it to return distinct companies only; In this case c.ID
> is the column i require to be unique.
>
> The UNION clause normally filters out duplicates, but because there is an
> extra column needed ('QueryNbr') to indicate which query the results were
> retrieve in (1 or 2) this filtering isn't having an effect.
>
> The UNION appears to look for duplicates on a row by row basic rather than a
> single column;
>
> Has anyone got any ideas on how to get around this issue?
>
> I am suffering with Access in this problem!!!
>
> Cheers,
> Adam

14 Jul 2006 2:47 PM

Bob Barrows [MVP]

AJ wrote:
Show quote

> Hi all,
>
> I have this monster query (at least i think it is).
>
> SELECT
>    c.ID, c.Company_Name, p.[level], 1 As QueryNbr
> FROM
>    (Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID)
> LEFT JOIN
>    Package AS p ON s.Package_ID = p.ID
> WHERE
>    c.Category = 'EXH'
> AND (s.ID = (SELECT Max(ID) FROM Sale WHERE Company_ID = c.ID) Or
> IsNull(s.ID))
> AND EXISTS(SELECT Company_ID FROM Event_Company_Link WHERE Event_ID =
> 20 AND Company_ID = c.ID)
> AND (INT(Start_Date) <= 38911) AND (INT(End_Date) >= 38911)
> AND Company_Name LIKE "% Inc% "
> ORDER BY p.[level] DESC , c.Company_Name, c.ID
> UNION
>    SELECT
>        c.ID, c.Company_Name, p.[level], 2 As QueryNbr
>    FROM (Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID)
>        LEFT JOIN Package AS p ON s.Package_ID = p.ID
>    WHERE c.Category = 'EXH'
>    AND (s.ID = (SELECT Max(ID) FROM Sale WHERE Company_ID = c.ID) Or
> IsNull(s.ID))
>    AND EXISTS(SELECT Company_ID FROM Event_Company_Link WHERE
> Event_ID = 20 AND Company_ID = c.ID)
>    AND Company_Name LIKE "%Inc%"
>    ORDER BY c.Company_Name, c.ID
>
> My issue is, i need it to return distinct companies only; In this
> case c.ID is the column i require to be unique.
>
> The UNION clause normally filters out duplicates, but because there
> is an extra column needed ('QueryNbr') to indicate which query the
> results were retrieve in (1 or 2) this filtering isn't having an
> effect.
>
> The UNION appears to look for duplicates on a row by row basic rather
> than a single column;
>
> Has anyone got any ideas on how to get around this issue?
>
> I am suffering with Access in this problem!!!
>

Create a saved query with this sql. For the sake of this example, call it
qUnionQuery.
Then create a new query that uses qUnionQuery in its FROM clause and groups
by the id field. You will need to provide aggregate functions for all the
other fields in the query (max or min will usually work)

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"