Solved

Join 2 tables, but only data in the first

Posted on 2014-09-09
4
177 Views
Last Modified: 2014-09-13
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.
0
Comment
Question by:tonelm54
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 40312382
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40312390
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
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 40312451
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40313846
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question