[Solved] ANY / IN approach for subquery in related databases

[mrmister]

Hi, I'd like to do a subquery to show how an example code could work, but I'm a bit lost...

I will attach two examples database.
To understand what I want to do... let's take a look to this database:

Please, download this database:

customers and agents.odb

https://mega.nz/#!4VZCHJYD!Jg8jT1bV8fAD ... FeXR03hPng

To summarize, in that database you have an AGENTS table and a CUSTOMERS table.
Due agents who are selling stuff can operate in several places, the CUSTOMERS table have a lot of fields related to customers, but one of them is the field AGENT_CODE
That field is also present in the table agents
What happens here is every record from every customer, customers table, have present the field AGENT_CODE, indicating that particular agent is operating for that customer's area (place).

Let's imagine I want to retrieve agents operating in certain the customer country, exactly in the UK.
Then, I can tackle this subquery approach typing this code:

Code: Select all

SELECT 
       "AGENT_CODE", 
       "AGENT_NAME", 
       "WORKING_AREA", 
       "COMMISSION" 

FROM "agents" 

WHERE "AGENT_CODE" IN ( SELECT "AGENT_CODE" FROM "customers" WHERE "CUST_COUNTRY" = 'UK' )

// WHERE "AGENT_CODE" ANY ( SELECT "AGENT_CODE" FROM "customers" WHERE "CUST_COUNTRY" = 'UK' )
// ANY WOULD PROCUDE THE SAME EFFECT

What I would retrive are agents operating in the customer's country UK.
What happened is the subquery retrieved those records from the table CUSTOMERS in which CUST_COUNTRY be UK, giving the associated agents to that particular record and showing them.

Okay.
That works fine.

My problem start now.

Please, download this database.

subqueryapproach.odb

https://mega.nz/#!lBQmiBra!XdN0jdfppmAr ... fDIg59Zvhw

I have a many to many relationship in three tables.

TABLE 1: EMPLEADOS (employees) a table in which I have the employees of a company
TABLE 2: DEPENDENCIAS (facilities) a table in which I have the places inside the building in which the employees are working. For example: ROOM 1A, ROOM 2B, MEETING ROOM, CELLAR 1, GENERAL PARKING, etc.
TABLE 3: USODEDEPENDENCIAS (a linking table to make possible the many to many relationship). The primary key from the tables above EMPLEADOS and DEPENDENCIAS, are present in this table, in this way I can assign rooms to employees granting them access (ACCESO in spanish). If you open this table you will see both primary keys, and the access granted to every employee.

Okay. That is perfect.

Let's imagine I want to see all my employees and I want to see the places they have granted access. Easy, I only have to type this code.

Code: Select all

SELECT 
       "tbl_EMPLEADOS"."Nombre", 
       "tbl_EMPLEADOS"."Apellido1ero", 
       "tbl_EMPLEADOS"."Apellido2seg", 
       "tbl_DEPENDENCIAS"."Dependencia", 
       "tbl_DEPENDENCIAS"."DepenDescripcion", 
       "tbl_DEPENDENCIAS"."Edificio", 
       "tbl_USODEDEPENDENCIAS"."MotivoAsignacion" 

FROM 
       "tbl_USODEDEPENDENCIAS", "tbl_EMPLEADOS", "tbl_DEPENDENCIAS" 

WHERE "tbl_USODEDEPENDENCIAS"."ID_Empleados" = "tbl_EMPLEADOS"."ID_Empleados" AND "tbl_USODEDEPENDENCIAS"."ID_Dependencia" = "tbl_DEPENDENCIAS"."ID_Dependencia" 

Observe WHERE is placing the one to many relationship and it is acting as the linking table for tbl_USODEDEPENDENCIAS assigning both primary keys for tables Empleados and Dependencias.

Let's imagine I want to filter this and retrive only those employees that have access to the CELLAR 2 (SOTANO 2)

Then I would only have to type this code.

Code: Select all

SELECT 
       "tbl_EMPLEADOS"."Nombre", 
       "tbl_EMPLEADOS"."Apellido1ero", 
       "tbl_EMPLEADOS"."Apellido2seg", 
       "tbl_DEPENDENCIAS"."Dependencia", 
       "tbl_DEPENDENCIAS"."DepenDescripcion", 
       "tbl_DEPENDENCIAS"."Edificio", 
       "tbl_USODEDEPENDENCIAS"."MotivoAsignacion" 

FROM 
       "tbl_USODEDEPENDENCIAS", "tbl_EMPLEADOS", "tbl_DEPENDENCIAS" 

WHERE "tbl_USODEDEPENDENCIAS"."ID_Empleados" = "tbl_EMPLEADOS"."ID_Empleados" AND "tbl_USODEDEPENDENCIAS"."ID_Dependencia" = "tbl_DEPENDENCIAS"."ID_Dependencia" 

AND  "tbl_DEPENDENCIAS"."Dependencia" = 'SOTANO 2' 

Perfect.

Okay, what I would like to do is exactly the same but typing a subquery, and my problem is I don't know how to type a subquery for this particular code.

In the first database customers and agents.odb, we did not have a relationship, because in every record from every customer we would have present the field AGENT_CODE, then we could type the subquery as follows...

Code: Select all

WHERE "AGENT_CODE" IN ( SELECT "AGENT_CODE" FROM "customers" WHERE "CUST_COUNTRY" = 'UK' )

But in the case of the subqueryapproach.odb database, I want do something similar. In the same fashion I did with the customers and agents.odb database, retrieving only those agents that be operating in the customer country UK, in this case, I want to retrieve those employees having access to the CELLAR 2 (SOTANO 2).

The code I wrote give that information perfectly, hower I would like to do exactly the same, in a subquery approach.

I'm stuck with that. Please, could someone give me an example or a bit of code to show me how to do that?

My problem is due we have the WHERE stablishing the linking relationships between tables, I cannot use it as WHERE ... IN and I don't know how to tackle a subquery approach.

Any ideas or suggestions?

Cheers

[Arineckaig]

The code I wrote give that information perfectly, hower I would like to do exactly the same, in a subquery approach.
I'm stuck with that. Please, could someone give me an example or a bit of code to show me how to do that?

Not sure why or what precisely you need as a sub-query, but this could be one approach:

Code: Select all

SELECT 
"tbl_EMPLEADOS"."Nombre",
"tbl_EMPLEADOS"."Apellido1ero",
"tbl_EMPLEADOS"."Apellido2seg",
"C"."Dependencia",
"C"."DepenDescripcion",
"C"."Edificio",
"tbl_USODEDEPENDENCIAS"."MotivoAsignacion"

FROM "tbl_EMPLEADOS",  
"tbl_USODEDEPENDENCIAS",
(SELECT * FROM "tbl_DEPENDENCIAS"
WHERE "tbl_DEPENDENCIAS"."Dependencia" = 'SOTANO 2') AS "C"

WHERE "tbl_USODEDEPENDENCIAS"."ID_Empleados" = "tbl_EMPLEADOS"."ID_Empleados" 
AND "tbl_USODEDEPENDENCIAS"."ID_Dependencia" = "C"."ID_Dependencia"

Sub-queries can cause issues with the DBMS optimizer, so I doubt this is necessarily the preferred route: it might however have potential merit in reducing one of the tables in the 3 table join to a single tuple.

The IN operator in SQL is applicable only to a (comma) list of values: it cannot be used with a multi-column record or table.

[mrmister]

Arineckaig, outstanding and brilliant !!!
I wanted exactly that, to tackle the same problem since a subquery approach (select).
But I see you are using also a temporary table "C", is that right?
I'm not very skilled with temporary tables, please could you elaborate a bit more, and explain the role of the C temporary table?
In the data to show you are showing Dependencia, DepenDescripcion, Edificio, and all that is being loaded in a temporary table C.

"C"."Dependencia",
"C"."DepenDescripcion",
"C"."Edificio",

Then you are loading all the data from the table Dependencias and filtering the field Dependencia to be equal to SOTANO 2, and you are doing... an alias with that in the temporary table C?
Is that right?

( SELECT *
FROM "tbl_DEPENDENCIAS"
WHERE "tbl_DEPENDENCIAS"."Dependencia" = 'SOTANO 2') AS "C"

So doing an alias is loading the data in C?

And finally you are stablishing the relationship between tables in the primary key field, to make possible all the data be shown?

"tbl_USODEDEPENDENCIAS"."ID_Dependencia" = "C"."ID_Dependencia"

Is that right?

I must confess, I still being a newbie with temporary tables, if you could explain me a bit the code, I'd be grateful.

AGAIN, BRILLIANT

[Arineckaig]

The sub-query:

Code: Select all

 (SELECT *
FROM "tbl_DEPENDENCIAS"
WHERE "tbl_DEPENDENCIAS"."Dependencia" = 'SOTANO 2')

creates a table (aka relation) that differs from the 'base' table named "tbl_DEPENDENCIAS". While every table in a FROM clause must have a name, it is also necessary to avoid ambiguity by giving a distinguishing name ("C" for example) to this replacement table: values are to included in the Join from columns that have the same name in the two tables. Generally if a table appears only in a subquery and not in the outer query, then columns from that table cannot be included in the output (the select list of the outer query).

Overall the revised SQL statement differs from that shown in the first post only by replacing one of the tables in the 3-way join with a table derived from a sub-query. It remains questionable, however, whether anything is gained by this change: it all depends on the size of your database and the optimizer supplied by the DBMS back-end. Sub-queries can be beneficial, but can also be detrimental: they are not a panacea and best used with care especially if NULL values are involved.

[mrmister]

Hi Arineckaig, first of all, my apologies for my delayed reply, but some health problems had me away from the forum some days.
Again thanks for your amazing help.

I have some problems understanding temporary databases, to understand well your code, if you find it right, I'll copy it, and try to do some comments.
I would appreciate you correct me in what I am wrong or just explain me a couple of doubts I have.

Basically I'm lost regarding how to load and show data from the real table to the temporary table.

Okay, I copy your code step by step... let's start.

A PART) WHAT I UNDERSTAND. Please, tell me if this is correct or not.

Code: Select all

SELECT
       "tbl_EMPLEADOS"."Nombre",
       "tbl_EMPLEADOS"."Apellido1ero",
       "tbl_EMPLEADOS"."Apellido2seg",
       "C"."Dependencia",
       "C"."DepenDescripcion",
       "C"."Edificio",
       "tbl_USODEDEPENDENCIAS"."MotivoAsignacion"

FROM "tbl_EMPLEADOS", 
     "tbl_USODEDEPENDENCIAS",

Okay, what I see there is we have real tables and a temporary table.
The real tables are tbl_EMPLEADOS, tbl_USODEPENDENCIAS
The temporary table is "C".

Is that right?

What I see that piece of code does it's selecting the data what will be shown in our query.
The data to be shown will be data from the real tables tbl_EMPLEADOS, tbl_USODEPENDENCIAS and the data from the temporary table "C".

SELECT will select the tables to show these data, which are the real tables tbl_EMPLEADOS, tbl_USODEPENDENCIAS and the data from the temporary table "C".
FROM will indicate where this data is COMING FROM, and that is the real tables tbl_EMPLEADOS, tbl_USODEPENDENCIAS

I OBSERVE THE FROM HAVE A COMMA... FROM "tbl_EMPLEADOS", "tbl_USODEDEPENDENCIAS",
THAT MEANS THE FROM CONTINUE AND YOU VERY INTELLIGENTLY ATTACHED THE SUBQUERY TO THE FROM.

In this way, the query will show also the data loaded in the temporary table "C".

Is that right?

Okay, let's go now with the subquery... attached to the FROM to make possible the data be shown.

B PART) WHAT I UNDERSTAND. Please, tell me if this is correct or not.

Code: Select all

( SELECT * 
  FROM "tbl_DEPENDENCIAS"
  WHERE "tbl_DEPENDENCIAS"."Dependencia" = 'SOTANO 2') AS "C"

You select all the fields SELECT * (asterisk mean selecting all the fields from the table) and you indicate where those fields come from, and that is FROM tbl_DEPENDENCIAS, so basically you are selecting all the data from the table tbl_DEPENDENCIAS.
Due we have a filter criterion for this particular example... and that is the retrieved data MUST MATCH with the SOTANO 2 facility (cellar 2 in english) you are applying a filter using the where, so you indicate WHERE "tbl_DEPENDENCIAS"."Dependencia" = 'SOTANO 2', in this way only the records pertaining to the SOTANO 2 will be retrieved...

AND... I DON'T UNDERSTAND THIS PART...

Code: Select all

AS "C"

As far as I now AS stand for AN ALIAS, and I understand an alias just alter the name of a field, changing its name.
However, in this case, seems to be the ALIAS IS ACTING AS A KIND OF DATA LOADER ALSO CREATING THE THE TEMPORARY TABLE "C"...

I did not know that...

Is this right?

And now the end part of the code... where relationships are stablished (I guess)...

C PART) WHAT I UNDERSTAND. Please, tell me if this is correct or not.

Code: Select all

WHERE "tbl_USODEDEPENDENCIAS"."ID_Empleados" = "tbl_EMPLEADOS"."ID_Empleados"

AND "tbl_USODEDEPENDENCIAS"."ID_Dependencia" = "C"."ID_Dependencia"

What I understand in this part of the code is...

You are using the WHERE to stablish a relationship for the primary keys of the tables... "tbl_USODEDEPENDENCIAS" and "ID_Empleados", observe they are equal = related by the ID (primary key field) with match in both tables "tbl_USODEDEPENDENCIAS"."ID_Empleados"... "tbl_EMPLEADOS"."ID_Empleados" and the relationship is stablished with... WHERE "tbl_USODEDEPENDENCIAS"."ID_Empleados" = "tbl_EMPLEADOS"."ID_Empleados"

So at this point we have a relationship between the table tbl_USODEDEPENDENCIAS and tbl_EMPLEADOS

HOWEVER WE HAVE NOW ALSO ANOTHER RELATIONSHIP... AND THAT IS WITH THE TEMPORARY TABLE "C"
Because you said also...

AND "tbl_USODEDEPENDENCIAS"."ID_Dependencia" = "C"."ID_Dependencia"

So besides, you relate the real table tbl_USODEDEPENDENCIAS and its primary key ID_Dependencia with the temporary table C and a primary key field created "on the fly", "C"."ID_Dependencia". Observe the primary key field ID_Dependencia is present in both tables, the real one... tbl_USODEDEPENDENCIAS and the temporary one C

What we have here then is:

You have related the "tbl_USODEDEPENDENCIAS with the table tbl_EMPLEADOS
AND ALSO
You have related the tbl_USODEDEPENDENCIAS with the temporary table "C"

Is this right?

TO CONCLUDE... WHAT I UNDERSTAND IS THIS:

1. SEE PART B) PLEASE. To understand the code we start with the subquery. You select all the data from the table tbl_DEPENDENCIAS... and you filter it, stablishing the condition that the retrieved data match in the field Dependencia with the word SOTANO 2 (cellar 2 in english). The reason you select all the data SELECT * is because you will finally load all this data in a temporary table named C, and for that you will use AN ALIAS... AS "C"

We continue...

2. SEE PART C) PLEASE. To understand the continue, we continue with the relationships. You stablish a relationship between two real tables, tbl_USODEDEPENDENCIAS and tbl_EMPLEADOS, using their respective primary keys... ID_Empleados. In this way all the data from tbl_USODEDEPENDENCIAS and tbl_EMPLEADOS will be related and available. However, the relationship does not end here because you continue with an AND... and you also relate the table tbl_USODEDEPENDENCIAS with the temporary table C. In this case you are using the primary key present in both tables, tbl_USODEDEPENDENCIAS and C, which is ID_Dependencia. We must note that tbl_USODEDEPENDENCIAS is having fields pertaining to the primary keys of different tables, ID_Empleados and ID_Dependencia. So you relate tbl_USODEDEPENDENCIAS and tbl_EMPLEADOS and also tbl_USODEDEPENDENCIAS with a temporary table named "C".

We continue...

3. SEE PART A) PLEASE. Finally to understand the code, we must go to the starting part of it. To make possible all the data be shown, from the real tables and also the temporary tables, you use a very tricky and smart solution... you select all the fields to perform the query with the first SELECT... then in the first FROM you select the real tables "tbl_EMPLEADOS", "tbl_USODEDEPENDENCIAS,
BUT BECAUSE YOU DO NOT CLOSE THE FROM, JUST YOU CONTINUE USING A COMMA, YOU ARE EMBEDDING THE SUBQUERY IN THE DATA TO BE SHOWN IN THE QUERY... AND YOU ARE USING AN ALIAS... AS "C" TO CREATE AND STABLISH THE TEMPORARY TABLE WITH THE NAME "C".

Is this right?
I'd appreciate your comments.

Cheers

[Arineckaig]

I have to confess to becoming confused.

In your first post you gave a good example that showed how to use a query to JOIN the data from 3 tables as is typically required for handling Many-to-Many relationships:

Code: Select all

    SELECT
           "tbl_EMPLEADOS"."Nombre",
           "tbl_EMPLEADOS"."Apellido1ero",
           "tbl_EMPLEADOS"."Apellido2seg",
           "tbl_DEPENDENCIAS"."Dependencia",
           "tbl_DEPENDENCIAS"."DepenDescripcion",
           "tbl_DEPENDENCIAS"."Edificio",
           "tbl_USODEDEPENDENCIAS"."MotivoAsignacion"
    FROM
           "tbl_USODEDEPENDENCIAS", "tbl_EMPLEADOS", "tbl_DEPENDENCIAS"
WHERE "tbl_USODEDEPENDENCIAS"."ID_Empleados" = "tbl_EMPLEADOS"."ID_Empleados" AND "tbl_USODEDEPENDENCIAS"."ID_Dependencia" = "tbl_DEPENDENCIAS"."ID_Dependencia"
AND "tbl_DEPENDENCIAS"."Dependencia" = 'SOTANO 2' 

You then indicated, without specifying a reason, that a JOIN did not meet your requirements:

mrmister stated:
Okay, what I would like to do is exactly the same but typing a subquery, and my problem is I don't know how to type a subquery for this particular code.

My previous reply merely demonstrated "how to type a subquery for this particular code", but did also caution whether that was necessarily a preferable alternative. Generally I would avoid sub-queries until you have a good understanding of, and experience with, simpler SELECT, FROM, WHERE and JOIN queries.

Working with any relational database makes frequent use of temporary tables, because these are in effect created every time a SELECT query statement is executed: in this respect a sub-query does not differ from any other query.

On the other hand if the content of a temporary table, produced by the output of a query or sub-query, is to be used as input for further processing it may frequently necessary to give that temporary (and not yet named) table an "alias" name so as to distinguish the values in its columns/attributes from similar named columns/attributes in other tables.

[mrmister]

Hi Arineckaig, first of all THANKS for your great help.
Zero confusion, as regard to me this sopic is Solved, and I will edit the message to mark it as Solved
The topic Temporary table creation in HSQLDB 1.8 for LibreOffice don't have any relationship with this topic.
I wanted to open a new thread to talk more about temporary tables because it was something that interested to me long time ago and I did not understand very well, so please, to clarify the forum's threads, to talk about the temporary tables, better please, we go to the other topic.

viewtopic.php?f=61&t=79680

For this topic, the ANY / IN approach for subquery... I just wanted to see a different point of view to tackle this query, and you did a great job.
The long message I posted before was to explain your code step by step.

Thanks a lot for your help.
I mark this as solved.