[Solved] Excluding values from a table in a query of another

[jmeyers]

Hi,
I'm developing a database to track hours and calculate overtime, hours per job, and such things. i'm replacing a spreadsheet that has all the functionality i need, but has gotten ridiculous. the only experience i have with databases is what i've gained so far on this project, so i apologize if this is a dumb question. i'm using ooo 3.3.0 on windows 7 pro x64. I have a query that totals hours grouped by person and week. I want to exclude from those totals entries whose date matches one of the dates listed in a table of holidays. the following query does the inverse of that, pulling only those hours that match holidays:

Code: Select all

SELECT "guys"."name", 
YEAR( "hours"."date" ) AS "year", 
case when dayofweek("hours"."date") = 1 then week("hours"."date")-1 else week("hours"."date") end as "week",
SUM( "hours"."hours" ) AS "total hours"-
FROM "guys", "hours", "jobs", "Holidays" 
WHERE "guys"."ID" = "hours"."guyID" AND "jobs"."ID" = "hours"."jobID" AND "hours"."date"="Holidays"."Holiday Date"
GROUP BY "guys"."name", "year","week" 
ORDER BY "guys"."name" ASC, "year" asc, "week" ASC

if i change the 6th line's ""hours"."date"="Holidays"."Holiday Date"" to have a != instead of the =, the results i get are wacky. how can change that to sum all the entries that are not in the holidays?

thanks in advance, and please let me know if any more information is needed.

-Jason

[rudolfo]

You are using a join query over 4 tables, that's quite a lot for someone with nearly no database experience. Anyway, it is correct how you do it. Your guys and hours tables are your working table and the holidays and jobs table are only used for filtering. Though I think you don't need the jobs table in this query because it is only appearing in the join condition.
Instead of using a join against the holidays table for filtering you can also use a "where in" filter with a subselect:

Code: Select all

SELECT ...
FROM "guys", "hours"
WHERE "guys"."ID" = "hours"."guyID" AND "hours"."date" IN (SELECT "Holidays"."Holiday Date" FROM "Holidays")
GROUP BY ...

Now to get the inverse set is pretty simple:

Code: Select all

WHERE "guys"."ID" = "hours"."guyID"
 AND "hours"."date" NOT IN (SELECT "Holidays"."Holiday Date" FROM "Holidays")

Some database support also a "WHERE EXISTS" construct, but personally I think the logic of WHERE IN is easier to catch. Although it is said to have the worst performance of the 3 approaches JOIN, where EXISTS and where IN. But if the holidays table is small (less the 50 or 100 rows) you won't see any performance difference.

[jmeyers]

Thanks so much, this worked exactly as i needed. Continuing to work on this, i've run into another snag. I now have the Queries "Holiday Hours"

Code: Select all

SELECT "Techs"."Name", YEAR( "Hours"."Date" ) AS "Year", CASE WHEN DAYOFWEEK( "Hours"."Date" ) = 1 THEN WEEK( "Hours"."Date" ) - 1 ELSE WEEK( "Hours"."Date" ) END AS "Week", SUM( "Hours"."Hours" ) AS "Holiday Hours" FROM "Techs", "Hours" WHERE "Techs"."ID" = "Hours"."TechID" AND "Hours"."Date" IN ( SELECT "Holidays"."Holiday Date" FROM "Holidays" ) GROUP BY "Techs"."Name", "Year", "Week" 

and "Not Holiday Hours"

Code: Select all

SELECT "Techs"."Name", YEAR( "Hours"."Date" ) AS "Year", CASE WHEN DAYOFWEEK( "Hours"."Date" ) = 1 THEN WEEK( "Hours"."Date" ) - 1 ELSE WEEK( "Hours"."Date" ) END AS "Week", SUM( CASE WHEN DAYOFWEEK( "Hours"."Date" ) != 1 THEN "Hours"."Hours" END ) AS "Weekday Hours", SUM( CASE WHEN DAYOFWEEK( "Hours"."Date" ) = 1 THEN "Hours"."Hours" END ) AS "Sunday Hours" FROM "Techs", "Hours" WHERE "Techs"."ID" = "Hours"."TechID" AND "Hours"."Date" NOT IN ( SELECT "Holidays"."Holiday Date" FROM "Holidays" ) GROUP BY "Techs"."Name", "Year", "Week" 

And I'm trying to make a third query which puts them together. the following "Total Hours" query

Code: Select all

select "Query Holiday Hours"."*",
"Query Not Holiday Hours"."*"
from "Query Holiday Hours","Query Not Holiday Hours" 
where "Query Holiday Hours"."Name"="Query Not Holiday Hours"."Name"AND"Query Holiday Hours"."Year"="Query Not Holiday Hours"."Year"
AND"Query Holiday Hours"."Week"="Query Not Holiday Hours"."Week"

only shows cases where the three matching fields (Name, Year, and Week) are the same, I'm looking for a way to have all the results from both query results, line them up by the matching fields are the same, and leave spaces when a datum only exists on one or the other column. the reason i want to do this is that i'm going to add a few columns that are calculated from the numbers presented.

Two other (hopefully) quick/easy questions:
-if i edit a query in sql view, close it, and reopen it, all my formatting and comments are gone. is there a way to preserve them?
-is there a resource which covers this stuff (the more advanced select statements) that is somewhere between the tutorials and the hsql reference docs?

thanks again.

-Jason

[rudolfo]

You have reached the point where you should visualize your data. I think what you want to see is:
"Holiday_data" is my short form for "all the fields in the query that are selected from the Holiday-Query". It's not only one field, it might consist of several fields. But with several fields the logic wouldn't be that easy to see.

Code: Select all

|week | Holiday_data | non_holiday_data
------+--------------+-----------------
   3  |              | 2345   | 123
   4  |   12 | 16    |  123   |  24
   5  |    7 | 11    |
   6  |              |   89   |  70

These would be the output of what SQL calls a full outer join of two tables: gaps can appear for the fields of both tables. Not all database engines support full outer joins. But all have the left outer join where the gaps can only appear for fields of the second table of the join (that would be only the rows of week 4 and 5 in the above data). A inner join would allow no gaps (that's only the row with week 4).

So if I understand you correctly the inner join is what you need. But you do not have table_A and table_B, but instead your two queries. You would need to convert the queries (frontend objects of the .odb document) into Views (named queries stored in the database) and use the names of the views after the FROM in the select statement.
Alternatively you can use sub-selects: This means after the FROM you don't specify a table name or a view name but direclty write a query inside paranthesis:

Code: Select all

SELECT .... 
 FROM (select ... from ... where ... group by ...) table_A
      JOIN ON (select ... from ... where ... group by ...) table_B WHERE table_A.week = table_B.week
 ORDER BY ...

Note, that I use generalized syntax (I hate HSQLDB because of flood of double quotes that it requires). SQL was build following natural language, quotes or backtics around names are undermining this original idea. They are for the stupid machines. If you use quotes in a sql statement this is just another way of saying "Yes, I am a slave to the machine."

[RPG]

Hello

Maybe this tutorial can help you.
http://www.dbbm.fiocruz.br/class/Lectur ... qltut.html
It is a small tutorial of SQL

I agree with rudolfo that it is maybe better to use a join in stead of the method you follow now. When you understand how the joins are working then the SQL line is more clear when you read it back. Read also the help file about query design. There is little but important information.

Romke

[jmeyers]

Thanks both of you for the help. Rudolfo, your visualized table shows exactly what i'm looking for. i want to do a full outer join. further reading suggests that hsql does not support that, but that the same result can be achieved by doing a left outer join, then a right outer join, then union the both of them. I cannot seem to get that union to work (I'm working in oobase's query window in sql mode), as it throws all sorts of errors, and i can't find any info that says how i can do the union. The code i have for the left outer join is

Code: Select all

SELECT "View Holiday Hours"."*", "View Not Holiday Hours"."*" FROM "View Holiday Hours" LEFT OUTER JOIN "View Not Holiday Hours" ON "View Holiday Hours"."Name" = "View Not Holiday Hours"."Name" AND "View Holiday Hours"."Year" = "View Not Holiday Hours"."Year" AND "View Holiday Hours"."Week" = "View Not Holiday Hours"."Week"

and the code that doesn't work for the full is

Code: Select all

select "View Holiday Hours"."*","View Not Holiday Hours"."*" from
(SELECT "View Holiday Hours"."*", "View Not Holiday Hours"."*" FROM "View Holiday Hours" LEFT OUTER JOIN "View Not Holiday Hours" ON "View Holiday Hours"."Name" = "View Not Holiday Hours"."Name" AND "View Holiday Hours"."Year" = "View Not Holiday Hours"."Year" AND "View Holiday Hours"."Week" = "View Not Holiday Hours"."Week"
union
SELECT "View Holiday Hours"."*", "View Not Holiday Hours"."*" FROM "View Holiday Hours" RIGHT OUTER JOIN "View Not Holiday Hours" ON "View Holiday Hours"."Name" = "View Not Holiday Hours"."Name" AND "View Holiday Hours"."Year" = "View Not Holiday Hours"."Year" AND "View Holiday Hours"."Week" = "View Not Holiday Hours"."Week")

how can i make this work, or am i approaching it the wrong way?

[rudolfo]

Activate the green check mark (direct sql mode). Normal queries are parsed twice: once by the Parser of OOoBase (frontend) and a second time by the database backend (in your case the HDBSQL engine). The Base parser is limited and it doesn't understand UNION.
With the green check mark you can skip the first parsing by the frontend and let only the backend engine parse the SQL statement.

But first you should make sure that your two queries from which you build the union can be run separately and have meaningful results.
Maybe you also search this forum for UNION and/or Direct sql mode ... this should come up with some interesting results. At least I remember that issues with unions and limitations of the frontend sql parser have been discussed quite often.

[DACM]

(I hate HSQLDB because of flood of double quotes that it requires). SQL was build following natural language, quotes or backtics around names are undermining this original idea. They are for the stupid machines. If you use quotes in a sql statement this is just another way of saying "Yes, I am a slave to the machine."

The strict use of quotes is imposed by Base, not HSQLDB. In fact, the default behavior of HSQLDB is to raise all un-quoted names to ALL_CAPS during processing. This behavior is the default for many database engines including Oracle, PostgreSQL, MySQL (configuration option), etc. This could certainly lead to the impression (misconception) that "SQL is case-insensitive."

• See: ALL_UPPERCASE names in the database:
  HSQLDB 1.8 > Chapter 9. SQL Syntax > SQL Expression > name

So HSQLDB is case-insensitive, unless object-names are encased in double-quotes. The use of quotes allows much more flexibility to include spaces within a name. With this flexibility comes additional awareness and effort in the form of quotes.

The original Base developers simply adopted the more flexible case-insensitive environment, while harnessing the user with the additional work involved (quotes). We can create a case-insensitive database using Base if we're careful to use ALL_CAPS when creating new database-objects. No spaces are allowed in this ALL_CAP convention but underscores may be used (MY_NAME). So if we create a column named FIRSTNAME, we can refer to it in Base using a 'direct-SQL' query as FirstName, or FIRSTname, or firstname, without quotes. Of course, if your query is 'analyzed' by the Base Query Parser, quotes become mandatory in Base, even for ALL_CAP names.

And of course, it follows that if we create a Name using any lower-case characters using Base GUI facilities, or otherwise enclose the "Name" in quotes, we get a case-sensitive name. All subsequent interaction with that "Name" will require the appropriate quotes. "Name" is now permanently distinct from "NAME" in the database.

[jmeyers]

Thanks all for your help, I was able to complete this piece of the project, and learn quite a bit in the process. I ended up using a lot of nested selects in a single view which pulls together all the data.

-Jason