Solved

Join 2 tables, but only data in the first

Posted on 2014-09-09
4
166 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
  • 2
4 Comments
 
LVL 32

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 49

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 32

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.​
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now