Solved

Join 2 tables, but only data in the first

Posted on 2014-09-09
4
181 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 34

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 34

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 49

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

729 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