Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Join 2 tables, but only data in the first

Posted on 2014-09-09
4
Medium Priority
?
192 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 36

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 52

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 36

Accepted Solution

by:
ste5an earned 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

578 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