Help with combining queries (1 Viewer)

bbulla

I'd rather be golfing
Local time
Today, 05:52
Joined
Feb 11, 2005
Messages
101
Hi,

Currently I am using 3 queries to come up with the results I am looking for, but I would like to figure out the proper way to combine these 3 into 1. What I am doing is looking at a table of inspections and picking out the ones that meet a certain criteria. Here are some details:


My first query looks at the table of inspections (Merge all HYDINSP Desc) and gets the most recent inspection (INSP_DATE) for each item (FACILITYID):

SELECT [Merge all HYDINSP Desc].FACILITYID, Max([Merge all HYDINSP Desc].INSP_DATE) AS MaxOfINSP_DATE
FROM [Merge all HYDINSP Desc]
GROUP BY [Merge all HYDINSP Desc].FACILITYID;


My second query looks at that same table of inspections and looks for all of the inspections that meet the criteria:

SELECT [Merge all HYDINSP Desc].*
FROM [Merge all HYDINSP Desc]
WHERE ((([Merge all HYDINSP Desc].DATEWORK) Is Null) AND (([Merge all HYDINSP Desc].HYDRANT_OPERATION)<>"OK")) OR ((([Merge all HYDINSP Desc].DATEWORK) Is Null) AND (([Merge all HYDINSP Desc].OPERATING_NUT)<>"OK")) OR ((([Merge all HYDINSP Desc].DATEWORK) Is Null) AND (([Merge all HYDINSP Desc].NOZZLE_CAPS)<>"OK")) OR ((([Merge all HYDINSP Desc].DATEWORK) Is Null) AND (([Merge all HYDINSP Desc].FLANGES)<>"OK")) OR ((([Merge all HYDINSP Desc].DATEWORK) Is Null) AND (([Merge all HYDINSP Desc].WATER_IN_BARREL)="True"));


My third criteria does a join on the first two queries, since in my final results I only want to see the most recent inspection that meets the criteria. I also :

SELECT [HYDINSP All WR].*
FROM [HYDINSP Most Recent] INNER JOIN [HYDINSP All WR] ON ([HYDINSP Most Recent].MaxOfINSP_DATE = [HYDINSP All WR].INSP_DATE) AND ([HYDINSP Most Recent].FACILITYID = [HYDINSP All WR].FACILITYID)
ORDER BY [HYDINSP All WR].INSP_DATE DESC;


Can anybody help me figure out how to do this in one SQL statement?? I prefer to use the GUI interface in Access, which I guess is why I've always broken it down it separate smaller queries, but there must be a way to do this in one SQL statement.

Thanks!
 

plog

Banishment Pending
Local time
Today, 04:52
Joined
May 11, 2011
Messages
11,613
First some pedanticism:

An SQL statement is one continous line of SQL code.
A query happens every time you use a "SELECT".

So, you can have one SQL statement that contains many queries--thus the term "subquery". Now the questions becomes --why? What does combining those 3 queries into one continous SQL statement do for you?

My guess is misstated your aim. You don't really care about how many queries nor SQL statements you have, you simply want to have one query object you can click on and have your data pop up in---the guts of the thing don't really matter.

If that's it, then what you want is too complicated to just talk through--we would need a copy of your database to play with and understand what the end data set is to look like.

Can you both confirm what I stated you want is correct and post a sample of your database?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:52
Joined
May 21, 2018
Messages
8,463
My question is why? If you think doing each part first is slower, I doubt it. You can do this all at one time instead of building the first individual queries by joining not on query 1 and query 2 but by joining on their select statements. Here is the first example off the internet.

Code:
ELECT DISTINCT Jg.[Jahrgang]
  FROM [Filterednew_hsbastudent] AS Stud
  LEFT JOIN
  [FilteredImageDescriptor] AS Im
  ON
    Stud.new_kontakt = Im.objectid
  LEFT JOIN [FilteredContact]    AS Cont
  ON
    Cont.contactid = Im.objectid
  INNER JOIN
        (SELECT
            pde_jahrgangid AS jahrgangid
            ,CONVERT(INT, new_jahrgang) AS [Jahrgang]
        FROM [dbo].[Filteredpde_jahrgang] 
        WHERE ISNUMERIC(new_jahrgang) = 1
        AND new_jahrgang IS NOT NULL
        )AS Jg
    ON
        Jg.jahrgangid = Stud.new_jahrgang   
WHERE
        Jg.Jahrgang > 2014

notice the Inner Join (Select....) instead of an Inner Join Query1

Although you can do this if good at SQL, you cannot write this in the VBE GUI. This may not optimize your query as you expect. Especially if you want the pieces to be resolved first.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:52
Joined
May 7, 2009
Messages
19,175
"smaller" queries are the way to go.
they are easy to troubleshoot.
 

bbulla

I'd rather be golfing
Local time
Today, 05:52
Joined
Feb 11, 2005
Messages
101
Hi,

Thanks for the responses.

The reason I need this to work in a single query is because I need to query the inspection table through an application called ArcMap, and it will only allow me to enter a single SQL statement. The actual table is really stored in a spatial database, so I guess you could say I am using MS Access as a way to test my SQL statement, since the ArcMap interface is very bad and doesn't give you much feedback when it encounters errors.

I've attached a sample of the DB table and the queries I am using.

I will mess with your suggestion MajP. I have been trying using a similar method, but never get the results I expect.
 

Attachments

  • SAMPLE.zip
    627.1 KB · Views: 239

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:52
Joined
May 7, 2009
Messages
19,175
the easiest way is save to tmp tables the first 2 queries.
use the 2 tmp tables on your 3rd (final) query.
 

plog

Banishment Pending
Local time
Today, 04:52
Joined
May 11, 2011
Messages
11,613
Ugh I don't envy you. You really do need this in just 1 SQL statement. Here's how I'd eat this elephant

First, do it all in Access---Get 1 query object to generate the data you want. If that means building queries on queries (as you have done--[HYDINSP Current WR Inspection] is based on the other 2 queries) then that's fine. Just reach the end in Access, make it so that you have 1 query object you can click on and it gives you the results you expect.

Second, I'd use a hierarchical query naming system in Access for all the queries you build--"sub1", "sub2", "sub3" which will let you know what order to put this thing together in ArcMap.

Third, move it from Access to ArcMap 1 query object at a time. The problem with SQL is there's isn't just 1 flavor: Access is different than Oracle is different from SQL Server is different from MySQL etc, etc. SQL on one database platform may not work on another, they overlap about 95% of the way, but that 5% is a killer. So move this piecemeal and make sure it each piece works.

Fourth, when moving the SQL of one query built on another query you will have to substitute that second queries SQL instead of its name. For example suppose the SQL of "sub2" in Access is this:

Code:
SELECT A, SUM(B) AS TotalB
FROM sub1
GROUP BY A

In ArcMap you will have to replace "sub1" with the actual SQL of sub1 like so:

Code:
SELECT A, SUM(B) AS TotalB
FROM
  (SELECT FieldA AS A, FieldB AS B
  FROM [Merge all HYDINSP Desc]
  WHERE FieldC=False
  ) AS sub1
GROUP BY A

Then when you have queries built on queries you just keep going down that rabbit hole of adding parenthesis and typing in the actual SQL instead of the query name.
 

bbulla

I'd rather be golfing
Local time
Today, 05:52
Joined
Feb 11, 2005
Messages
101
Thanks Plog. Yes, ArcMap is not the best for querying data. Even just trying my first query to show only the most recent inspection keeps crapping out in ArcMap. I think it doesn't like the Max operator on the date field.

Anyways, I will keep plugging away. The end goal is to create a query layer based on the Inspection table stored in the database. From there I'm hoping to publish that table as a webservice in our Portal, but that might be wishful thinking.

Thanks!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:52
Joined
May 21, 2018
Messages
8,463
I am familiar with what ArcMap is, but never play with the application. Can you directly access the backend database instead of going through the application?
 

bbulla

I'd rather be golfing
Local time
Today, 05:52
Joined
Feb 11, 2005
Messages
101
Yes. I can connect to all the tables through MS Access....ODBC Connection.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:52
Joined
May 21, 2018
Messages
8,463
Yes. I can connect to all the tables through MS Access....ODBC Connection.
At least that is good. I thought you had to just drop a sql string directly into the app in some scripting language.
As for the MAX operator this page lists some of the different functions supported by different rdms
I see mysql, sqlserver, and access functions listed
 

bbulla

I'd rather be golfing
Local time
Today, 05:52
Joined
Feb 11, 2005
Messages
101
To follow up on this, I have gotten a single query to work in MS Access:

SELECT * from

(SELECT works_INSPECTIONS_HYDRANT_INSPECTION.*
FROM works_INSPECTIONS_HYDRANT_INSPECTION
WHERE (((works_INSPECTIONS_HYDRANT_INSPECTION.DATEWORK) Is Null) AND ((works_INSPECTIONS_HYDRANT_INSPECTION.HYDRANT_OPERATION)<>"OK")) OR (((works_INSPECTIONS_HYDRANT_INSPECTION.DATEWORK) Is Null) AND ((works_INSPECTIONS_HYDRANT_INSPECTION.OPERATING_NUT)<>"OK")) OR (((works_INSPECTIONS_HYDRANT_INSPECTION.DATEWORK) Is Null) AND ((works_INSPECTIONS_HYDRANT_INSPECTION.NOZZLE_CAPS)<>"OK")) OR (((works_INSPECTIONS_HYDRANT_INSPECTION.DATEWORK) Is Null) AND ((works_INSPECTIONS_HYDRANT_INSPECTION.FLANGES)<>"OK")) OR (((works_INSPECTIONS_HYDRANT_INSPECTION.DATEWORK) Is Null) AND ((works_INSPECTIONS_HYDRANT_INSPECTION.WATER_IN_BARREL)="True"))) ALL_WR_Table

INNER JOIN

(SELECT Max(works_INSPECTIONS_HYDRANT_INSPECTION.INSP_DATE) AS MaxOfINSP_DATE, works_INSPECTIONS_HYDRANT_INSPECTION.FACILITYID
FROM works_INSPECTIONS_HYDRANT_INSPECTION
GROUP BY works_INSPECTIONS_HYDRANT_INSPECTION.FACILITYID
ORDER BY works_INSPECTIONS_HYDRANT_INSPECTION.FACILITYID) MOST_RECENT_Table

ON

(ALL_WR_Table.FACILITYID=MOST_RECENT_Table.FACILITYID) AND (ALL_WR_Table.INSP_DATE =MOST_RECENT_Table.MaxOfINSP_DATE)


Not getting this to work in ArcMap will be a whole other story.

Thanks everyone for your help. Now on to the ArcMap forums! :cool:



1613758278113.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:52
Joined
May 21, 2018
Messages
8,463
for easier reading
SQL:
SELECT works_inspections_hydrant_inspection.*
FROM   works_inspections_hydrant_inspection
WHERE  (((
                            works_inspections_hydrant_inspection.datework) IS NULL)
       AND    ((
                            works_inspections_hydrant_inspection.hydrant_operation)<>"OK"))
OR     (((
                            works_inspections_hydrant_inspection.datework) IS NULL)
       AND    ((
                            works_inspections_hydrant_inspection.operating_nut)<>"OK"))
OR     (((
                            works_inspections_hydrant_inspection.datework) IS NULL)
       AND    ((
                            works_inspections_hydrant_inspection.nozzle_caps)<>"OK"))
OR     (((
                            works_inspections_hydrant_inspection.datework) IS NULL)
       AND    ((
                            works_inspections_hydrant_inspection.flanges)<>"OK"))
OR     (((
                            works_inspections_hydrant_inspection.datework) IS NULL)
       AND    ((
                            works_inspections_hydrant_inspection.water_in_barrel)="True"))) all_wr_table INNER JOIN
(
         SELECT   max(works_inspections_hydrant_inspection.insp_date) AS maxofinsp_date,
                  works_inspections_hydrant_inspection.facilityid
         FROM     works_inspections_hydrant_inspection
         GROUP BY works_inspections_hydrant_inspection.facilityid
         ORDER BY works_inspections_hydrant_inspection.facilityid) most_recent_table ON (
  all_wr_table.facilityid=most_recent_table.facilityid
)
AND
(
  all_wr_table.insp_date =most_recent_table.maxofinsp_date
)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:52
Joined
May 21, 2018
Messages
8,463
The reason you get that error is
Code:
works_inspections_hydrant_inspection.*
You need to select all the fields specifially.
In that * of fields, is likely the field facilityID. And since you have faciility ID in other tables it does not know which table that came from.

If you still get the error consider aliasing your tables and at least each facility id. I always thought the table alias came after the FROM table. I never seen it like that. I would think more like
SQL:
SELECT all_wr_table.Field1, all_wr_table.field2...
FROM   works_inspections_hydrant_inspection As all_wr_table
WHERE  (((
                            all_wr_table.datework) IS NULL)
       AND    ((
                            all_wr_table.hydrant_operation)<>"OK"))
OR     (((
                           all_wr_table.datework) IS NULL)
       AND    ((
                            all_wr_table.operating_nut)<>"OK"))
OR     (((
                           all_wr_table.datework) IS NULL)
       AND    ((
                            all_wr_table.nozzle_caps)<>"OK"))
OR     (((
                            all_wr_table.datework) IS NULL)
       AND    ((
                            all_wr_table.flanges)<>"OK"))
OR     (((
                            all_wr_table.datework) IS NULL)
       AND    ((
                            all_wr_table.water_in_barrel)="True"))) INNER JOIN
(
         SELECT   max(most_recent_table.insp_date) AS maxofinsp_date,
                  most_recent_table.facilityid
         FROM     works_inspections_hydrant_inspection as most_recent_table
         GROUP BY most_recent_table.facilityid
         ORDER BY most_recent_table.facilityid) ON (
  all_wr_table.facilityid = most_recent_table.facilityid
)
AND
(
  all_wr_table.insp_date = most_recent_table.maxofinsp_date
)
 
Last edited:

bbulla

I'd rather be golfing
Local time
Today, 05:52
Joined
Feb 11, 2005
Messages
101
Hi MajP,

Yes, specifying each field with the ALL_WR_Table.<fieldname> solved the problem. In Access it would just show both of my FACILITYID fields (one from each table), with no error message.

Thanks for your help!! It's nice to get this working.
 

Users who are viewing this thread

Top Bottom