Query Help.

[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

 

[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!
***************************

 

[Bob Barrows [MVP]]

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)