Join 2 tables, but only data in the first

Im trying to join 2 queries together, the first being:-
SELECT [Fault Logs].*, [_Users].[Job Title], [_Users].Branch, [Fault Logs].Status, 1 AS Prioity
FROM [Fault Logs] INNER JOIN _Users ON [Fault Logs].User = [_Users].User
WHERE ((([_Users].[Job Title]) Like "Director" Or ([_Users].[Job Title]) Like "Manager" Or ([_Users].[Job Title]) Like "General Manager" Or ([_Users].[Job Title]) Like "Business Unit General Manager") AND (([Fault Logs].Status) Like "Awaiting Tech")) OR ((([_Users].Branch) Like "Head Office") AND (([Fault Logs].Status) Like "Awaiting Tech")) OR ((([_Users].Branch) Like "Head Office") AND (([Fault Logs].Status) Like "Awaiting Tony"))

Open in new window

and the second being
SELECT [Fault Logs].*, [_Users].[Job Title], [_Users].Branch, [Fault Logs].Status, 2 AS Prioity
FROM [Fault Logs] INNER JOIN _Users ON [Fault Logs].User = [_Users].User
WHERE (([Fault Logs].Status) Like "Awaiting Tech") OR (([Fault Logs].Status) Like "Awaiting Tony")

Open in new window


Both work fine, so I joined them together using 'UNION':-
(SELECT [Fault Logs].*, [_Users].[Job Title], [_Users].Branch, [Fault Logs].Status, 1 AS Prioity
FROM [Fault Logs] INNER JOIN _Users ON [Fault Logs].User = [_Users].User
WHERE ((([_Users].[Job Title]) Like "Director" Or ([_Users].[Job Title]) Like "Manager" Or ([_Users].[Job Title]) Like "General Manager" Or ([_Users].[Job Title]) Like "Business Unit General Manager") AND (([Fault Logs].Status) Like "Awaiting Tech")) OR ((([_Users].Branch) Like "Head Office") AND (([Fault Logs].Status) Like "Awaiting Tech")) OR ((([_Users].Branch) Like "Head Office") AND (([Fault Logs].Status) Like "Awaiting Tony")))

UNION

(SELECT [Fault Logs].*, [_Users].[Job Title], [_Users].Branch, [Fault Logs].Status, 2 AS Prioity
FROM [Fault Logs] INNER JOIN _Users ON [Fault Logs].User = [_Users].User
WHERE (([Fault Logs].Status) Like "Awaiting Tech") OR (([Fault Logs].Status) Like "Awaiting Tony"));

Open in new window


Which again works fine, however I have an issue ordering the values, as I want to order by Prioity which exists in both tables, but I cant order both tables. My other problem is I have duplicated data which I thought I could try and put as 'distinct', however as the prioity is different it still shows as they are different. What I need is to show the first table, and if it exists in the second table dont show it in the second table (showing priory 2).

Im using Microsoft Access 2013 (I am trying to upgrade to MySQL but time is against me).

Any suggestions how I can solve my issue? My initial idea was to use a temporary table, however Access doesnt allow temporary tables.
tonelm54Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
E.g.

SELECT  *
FROM    ( SELECT    [Fault Logs].* ,
                    [_Users].[Job Title] ,
                    [_Users].Branch ,
                    [Fault Logs].[Status] ,
                    1 AS Prioity
          FROM      [Fault Logs]
                    INNER JOIN _Users ON [Fault Logs].[User] = [_Users].[User]
          WHERE     ( [_Users].[Job Title] IN ( "Director", "Manager", "General Manager", "Business Unit General Manager" )
                      AND [Fault Logs].[Status] = "Awaiting Tech"
                    )
                    OR ( [_Users].Branch = "Head Office"
                         AND [Fault Logs].[Status] IN ( "Awaiting Tech", "Awaiting Tony" )
                       )
          UNION
          SELECT    [Fault Logs].* ,
                    [_Users].[Job Title] ,
                    [_Users].Branch ,
                    [Fault Logs].[Status] ,
                    2 AS Prioity
          FROM      [Fault Logs]
                    INNER JOIN _Users ON [Fault Logs].[User] = [_Users].[User]
          WHERE     [Fault Logs].[Status] IN ( "Awaiting Tech", "Awaiting Tony" )
        ) Q
ORDER BY Q.Prioity;

Open in new window


Sounds like you need an EXISTS() predicate or non-equi JOIN.
0
Gustav BrockCIOCommented:
Use the union query as source in a second query where you Group By all fields except Priority for which you choose either Min or Max.

/gustav
0
ste5anSenior DeveloperCommented:
Or no UNION at all:

 SELECT [Fault Logs].* ,
        [_Users].[Job Title] ,
        [_Users].Branch ,
        [Fault Logs].[Status] ,
        IIF(( [_Users].[Job Title] IN ( "Director", "Manager", "General Manager", "Business Unit General Manager" )
              AND [Fault Logs].[Status] = "Awaiting Tech"
            )
        OR ( [_Users].Branch = "Head Office"
             AND [Fault Logs].[Status] IN ( "Awaiting Tech", "Awaiting Tony" )
           ), 1, 2) AS Prioity
 FROM   [Fault Logs]
        INNER JOIN _Users ON [Fault Logs].[User] = [_Users].[User]
 WHERE  ( [_Users].[Job Title] IN ( "Director", "Manager", "General Manager", "Business Unit General Manager" )
          AND [Fault Logs].[Status] = "Awaiting Tech"
        )
        OR ( [_Users].Branch = "Head Office"
             AND [Fault Logs].[Status] IN ( "Awaiting Tech", "Awaiting Tony" )
           )
        OR [Fault Logs].[Status] IN ( "Awaiting Tech", "Awaiting Tony" );
        

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
no points please, I just wish to make one point

UNION is more "expensive" than UNION ALL because UNION tries to produce "unique rows" (similar to select distinct)

If you have 2 queries, and you place " 1 AS Prioity" in one and " 2 AS Prioity" in the other,
then you remove any ability for a row in the first query to be exactly the same as a row in the second query
and so UNION cannot find any matching rows and hence this extra expense of UNION is just wasted.

and in such circumstances; you should use UNION ALL instead


--
as a final note looks like you can avoid both UNION and UNION ALL here, which is a good thing
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.