Query Help.

Discussion in 'ASP General' started by AJ, Jul 14, 2006.

  1. AJ

    AJ Guest

    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
    AJ, Jul 14, 2006
    #1
    1. Advertising

  2. 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!
    ***************************


    "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
    Cowboy (Gregory A. Beamer) - MVP, Jul 14, 2006
    #2
    1. Advertising

  3. 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!!!
    >

    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"
    Bob Barrows [MVP], Jul 14, 2006
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Learner
    Replies:
    1
    Views:
    976
    Marina Levit [MVP]
    Jan 30, 2006
  2. Anonymous
    Replies:
    0
    Views:
    1,445
    Anonymous
    Oct 13, 2005
  3. David Gordon

    xpath query query

    David Gordon, May 18, 2005, in forum: XML
    Replies:
    2
    Views:
    781
    David Gordon
    May 18, 2005
  4. Eric Nelson
    Replies:
    5
    Views:
    1,511
    Alexey Smirnov
    Feb 4, 2009
  5. Alpha Blue
    Replies:
    3
    Views:
    392
    Alpha Blue
    Jul 28, 2009
Loading...

Share This Page