Using a subquery, just one column with csv result, as a set for using IN in the query

P

pacctono

Member
Local time
Yesterday, 23:38
Joined
Jun 13, 2022
Messages
66
Hello, friends!

I have a simple query, that works as expected:
Code: 
SELECT c.tx_clave, c.tx_descripcion
FROM no_conceptos AS c
WHERE c.tx_clave In ('A01','A04','A07','A08','A10','A13','A16','A19','A22','A25','A84')

But I would like to substitute the set "'A01','A04','A07','A08','A10','A13','A16','A19','A22','A25','A84'" with a subquery
Code: 
SELECT c.tx_clave, c.tx_descripcion
FROM no_conceptos AS c
WHERE c.tx_clave In (SELECT g.tx_claves FROM no_grupos AS g WHERE g.tx_grupo = '05');
g.tx_claves=:'A01','A04','A07','A08','A10','A13','A16','A19','A22','A25','A84'; WHERE g.tx_grupo = '05'

I know the problem is that the subquery result is a string as a whole, but is there a way to get the right answer? any help?
 
> the subquery result is a string as a whole
No, it isn't. It is a query that returns a set of data. The IN clause then essentially turns that into an inner join. You can do that too: join the two tables and set your Where condition.
 
tvanstiphout said:
> the subquery result is a string as a whole
No, it isn't. It is a query that returns a set of data. The IN clause then essentially turns that into an inner join. You can do that too: join the two tables and set your Where condition.
Click to expand...
Well, the first query returns 11 rows, as expected. The second one returns none.
 
Well, the first query returns 11 rows, as expected. The second one returns none.
Click to expand...

Did you stop there? You now need to manually look into no_conceptos.tx_clave and see if those 11 results are actually in there. My guess is that they are not.
 
plog said:
Did you stop there? You now need to manually look into no_conceptos.tx_clave and see if those 11 results are actually in there. My guess is that they are not.
Click to expand...
If I execute this code:
Code: 
SELECT c.tx_clave, c.tx_descripcion, c.tx_grupo
FROM no_conceptos AS c
WHERE c.tx_clave In ('A01','A04','A07','A08','A10','A13','A16','A19','A22','A25','A84');
I get this:
Code: 
tx_clave    tx_descripcion    tx_grupo
A01    SUELDO MENSUAL    00
A04    PRIMA COMPLEMENTARIA    01
A07    BONO POR RESPONSABILIDAD (JEFATURA)    01
A08    PRIMA POR JEFATURA    01
A10    PRIMA POR HIJOS    03
A13    PRIMA DE PROFESIONALIZACION    01
A16    PRIMA POR ANTIGÜEDAD    01
A19    FERIADO    02
A22    BONO NOCTURNO    02
A25    BONO NOCTURNO JORNADA MIXTA    00
A84    APORTE IPSPUDO FAOV    02

Ad f I execute this code:
Code: 
SELECT g.tx_grupo, g.tx_descripcion, g.tx_claves
FROM no_grupos AS g;
I get this:
Code: 
tx_grupo    tx_descripcion    tx_claves
00    NO APLICABLE  
01    SUELDO MENSUAL    'A01'
02    SUELDO INTEGRAL    'A01','A04','A07','A08','A10','A13','A16','A18','A19','A22'
03    APLICADO A LA PRIMA POR HIJOS  
04    SUELDO AGREGADO. APLICADO A LA CUOTA IPSPUDO  
05    CALCULO DEL BONO VACACIONAL/FIN DE AÑO    'A01','A04','A07','A08','A10','A13','A16','A19','A22','A25','A84'

If I execute this code:
Code: 
SELECT c.tx_clave, c.tx_descripcion, c.tx_grupo
FROM no_conceptos AS c
WHERE c.tx_clave In ('A01','A04','A07','A08','A10','A13','A16','A19','A22','A25','A84')
I get:
Code: 
tx_clave    tx_descripcion    tx_grupo
A01    SUELDO MENSUAL    00
A04    PRIMA COMPLEMENTARIA    01
A07    BONO POR RESPONSABILIDAD (JEFATURA)    01
A08    PRIMA POR JEFATURA    01
A10    PRIMA POR HIJOS    03
A13    PRIMA DE PROFESIONALIZACION    01
A16    PRIMA POR ANTIGÜEDAD    01
A19    FERIADO    02
A22    BONO NOCTURNO    02
A25    BONO NOCTURNO JORNADA MIXTA    00
A84    APORTE IPSPUDO FAOV    02

But if I execute:
Code: 
SELECT c.tx_clave, c.tx_descripcion
FROM no_conceptos AS c
WHERE c.tx_clave In (SELECT g.tx_claves FROM no_grupos AS g WHERE g.tx_grupo = '05');
I get: nothing.

By the way I could solve this with vba. Executing the subquery first and putting the result in its place. But I would like a solution with just SQL.
 
Short version--you've set up your tables incorrectly and should not be jamming a bunch of values into one field.

Long version -- Let's look at just one example from that second query:

05 CALCULO DEL BONO VACACIONAL/FIN DE AÑO 'A01','A04','A07','A08','A10','A13','A16','A19','A22','A25','A84'

Technically though those are all text fields which means each field's results are implicitly surrounded by quotes like so:

"05" "CALCULO DEL BONO VACACIONAL/FIN DE AÑO" "'A01','A04','A07','A08','A10','A13','A16','A19','A22','A25','A84'"

3 fields, 3 sets of double quotes. That means your last isn't 11 values surrounded by 11 pairs of single quotes, but 1 field surrounded by 1 pair of double quotes. That means when you use it in the first query it is trying to find a match for everything inside the double quotes, not each individual item you expect to be inside the single quotes:

So, if you have "A13" in your first it's not going to find a match because "A13" does not equal ""'A01','A04','A07','A08','A10','A13','A16','A19','A22','A25','A84'"".

You need to break no_grupos out into 2 tables--one table for tx_grupo and tx_descripcion values (and also an autonumber primary key id--tx_ID) and one table for tx_claves (and the foreign key tx_ID back to the first table). Then you need to have each tx_claves value in its own record, not jammed into one field.
 
pacctono said:
g.tx_claves=:'A01','A04','A07','A08','A10','A13','A16','A19','A22','A25','A84'; WHERE g.tx_grupo = '05'
Click to expand...
Are you saying that whole comma-delimited string is the value in the field for a single record (WHERE g.tx_grupo = '05') ?

It will certainly make your life more difficult.

Horrible, but you can try something like:
SQL: 
SELECT
  c.tx_clave,
  c.tx_descripcion
FROM no_conceptos AS c
WHERE InStr(',' & DLookup("tx_claves", "no_grupos", "g.tx_grupo = '05'") & ',', ",'" & c.tx_clave & "',") > 0;
 
cheekybuddha said:
Are you saying that whole comma-delimited string is the value in the field for a single record (WHERE g.tx_grupo = '05') ?

It will certainly make your life more difficult.

Horrible, but you can try something like:
SQL: 
SELECT
  c.tx_clave,
  c.tx_descripcion
FROM no_conceptos AS c
WHERE InStr(',' & DLookup("tx_claves", "no_grupos", "g.tx_grupo = '05'") & ',', ",'" & c.tx_clave & "',") > 0;
Click to expand...
Horrible, too, but works:
Code: 
SELECT c.tx_clave, c.tx_descripcion
FROM no_conceptos AS c
WHERE instr(dlookup("tx_claves","no_grupos","tx_grupo = '05'"),c.tx_clave) >0;
 
You can use the eval function if you want

if your string is

A01,A04,A07,A08,A10,A13,A16,A19,A22,A25,A84

i.e. without the quotes, they can be added in using the replace function


WHERE Eval("'" & c.tx_clave & "' In ('" & replace(tx_claves,",","','") & "')")=true

in the immediate window
?eval("'" & "A01" & "' IN ('" & replace("A01,A04,A07,A08,A10,A13,A16,A19,A22,A25,A84",",","','") & "')")
-1

?eval("'" & "A41" & "' IN ('" & replace("A01,A04,A07,A08,A10,A13,A16,A19,A22,A25,A84",",","','") & "')")
0

if you have the quotes then it is just

WHERE Eval("'" & c.tx_clave & "' In (" & tx_claves & ")")=true

?eval("'" & "A01" & "' IN (" & "'A01','A04','A07','A08','A10','A13','A16','A19','A22','A25','A84'" & ")")
-1

?eval("'" & "A41" & "' IN (" & "'A01','A04','A07','A08','A10','A13','A16','A19','A22','A25','A84'" & ")")
0
 
Pat Hartman said:
Do NOT use a domain function inside a query or a loop. It runs a separate query for each row. Use the join that I suggested.
Click to expand...
Thanks:
Code: 
SELECT c.tx_clave, c.tx_descripcion
FROM no_conceptos AS c, no_grupos as g
WHERE g.tx_grupo = '05' And instr(g.tx_claves, c.tx_clave) > 0;

Trying with this, make me to create a more complicate query that I was really looking for:
Code: 
SELECT n.lo_cedula, p.tx_nombre, c.tx_grupo, Round([c].[do_factor]*Sum([n].[mo_monto]+[n].[mo_eventual]),2) AS [Bono Fin Año]
FROM no_conceptos AS c INNER JOIN no_grupos AS g ON g.tx_grupo = c.tx_grupo, no_nominas AS n INNER JOIN no_personal AS p ON n.lo_cedula = p.lo_cedula
WHERE (((p.bo_retirado)=False) And ((c.tx_clave)='A98') And instr(g.tx_claves,n.tx_clave)>0)
GROUP BY n.lo_cedula, p.tx_nombre, c.tx_grupo, c.do_factor
ORDER BY p.tx_nombre;
 
Using a join would look more like:
SQL: 
SELECT
  c.tx_clave,
  c.tx_descripcion
FROM no_conceptos AS c
INNER JOIN no_grupos AS g
        ON g.tx_grupo = '05'
       AND InStr(g.tx_claves, c.tx_clave) > 0
;
 
Pat Hartman said:
I think the only reason that clave was in the original query was because the OP didn't know how to use a join to the group table to select the entire group of claves. Either you select specific claves as he was doing or you use a join and select the entire group.
Click to expand...
I don't follow (but that's not unusual!)

The problem is that no_grupos.tx_claves are comma delimited strings of multiple values 😖

But maybe your solution bypasses that?
 
pacctono said:
Ad f I execute this code:
Code: 
SELECT g.tx_grupo, g.tx_descripcion, g.tx_claves
FROM no_grupos AS g;
I get this:
Code: 
tx_grupo    tx_descripcion    tx_claves
00    NO APLICABLE 
01    SUELDO MENSUAL    'A01'
02    SUELDO INTEGRAL    'A01','A04','A07','A08','A10','A13','A16','A18','A19','A22'
03    APLICADO A LA PRIMA POR HIJOS 
04    SUELDO AGREGADO. APLICADO A LA CUOTA IPSPUDO 
05    CALCULO DEL BONO VACACIONAL/FIN DE AÑO    'A01','A04','A07','A08','A10','A13','A16','A19','A22','A25','A84'
Click to expand...
Above from the OP in Post #1
 
Sorry Pat, I said @pacctono posted the data in Post #1 - it should have read Post #5

Reformatting the data to make it more readable:
Using query:
SQL: 
SELECT c.tx_clave, c.tx_descripcion, c.tx_grupo
FROM no_conceptos AS c
WHERE c.tx_clave In ('A01','A04','A07','A08','A10','A13','A16','A19','A22','A25','A84');
yields:
Code: 
tx_clave    tx_descripcion                          tx_grupo
A01         SUELDO MENSUAL                          00
A04         PRIMA COMPLEMENTARIA                    01
A07         BONO POR RESPONSABILIDAD (JEFATURA)     01
A08         PRIMA POR JEFATURA                      01
A10         PRIMA POR HIJOS                         03
A13         PRIMA DE PROFESIONALIZACION             01
A16         PRIMA POR ANTIGÜEDAD                    01
A19         FERIADO                                 02
A22         BONO NOCTURNO                           02
A25         BONO NOCTURNO JORNADA MIXTA             00
A84         APORTE IPSPUDO FAOV                     02

Using query:
SQL: 
SELECT g.tx_grupo, g.tx_descripcion, g.tx_claves
FROM no_grupos AS g;
yields:
Code: 
tx_grupo    tx_descripcion                                  tx_claves
00          NO APLICABLE
01          SUELDO MENSUAL                                  'A01'
02          SUELDO INTEGRAL                                 'A01','A04','A07','A08','A10','A13','A16','A18','A19','A22'
03          APLICADO A LA PRIMA POR HIJOS
04          SUELDO AGREGADO. APLICADO A LA CUOTA IPSPUDO
05          CALCULO DEL BONO VACACIONAL/FIN DE AÑO          'A01','A04','A07','A08','A10','A13','A16','A19','A22','A25','A84'
 
Friends,

This is the solution for my question:

Code: 
SELECT c.tx_clave, c.tx_descripcion
FROM no_conceptos AS c, no_grupos as g
WHERE g.tx_grupo = '05' And instr(g.tx_claves, c.tx_clave) > 0;

It gives what I was looking for
 
Pat Hartman said:
The entire point of the join was to eliminate the instr()
Click to expand...
I don't see how the OP can eliminate the InStr() without properly normalising the tables, and not having multiple values stuffed as a string in to no_grupos.tx_claves.

Pat Hartman said:
If you do need the instr(), then you DON"T need the join and the group = 5
Click to expand...
With the example data shown, wouldn't that erroneously return the result of 'A18' for no_conceptos.tx_clave which is present where tx_grupo = 2 but not where tx_grupo = 5

@pacctono , did the query with join in Post #16 not work?
 
cheekybuddha said:
Using a join would look more like:
SQL: 
SELECT
  c.tx_clave,
  c.tx_descripcion
FROM no_conceptos AS c
INNER JOIN no_grupos AS g
        ON g.tx_grupo = '05'
       AND InStr(g.tx_claves, c.tx_clave) > 0
;
Click to expand...

cheekybuddha said:
I don't see how the OP can eliminate the InStr() without properly normalising the tables, and not having multiple values stuffed as a string in to no_grupos.tx_claves.


With the example data shown, wouldn't that erroneously return the result of 'A18' for no_conceptos.tx_clave which is present where tx_grupo = 2 but not where tx_grupo = 5

@pacctono , did the query with join in Post #16 not work?
Click to expand...
Yes, Thanks!. I just have to add parentesis

Code: 
SELECT
  c.tx_clave,
  c.tx_descripcion
FROM no_conceptos AS c
INNER JOIN no_grupos AS g
        ON (g.tx_grupo = '05'
       AND InStr(g.tx_claves, c.tx_clave) > 0)
;
 
Pat Hartman said:
You join to the group table on clave which would seem to bring back two rows
Click to expand...
How do you propose to do this without using InStr() (or some other, perhaps LIKE ?)
Code: 
"A01" != "'A01','A04','A07','A08','A10','A13','A16','A19','A22','A25','A84'"

Pat Hartman said:
1. Join to the group table on clave and add criteria to select by group. This selects ALL claves that belong to a group WITHOUT the need to specify them in an instr.
Click to expand...
Can you give an example of the query you propose here in SQL? I'm being really dense and can't see how this would work.

Pat Hartman said:
The tables are almost normalized.
Click to expand...
Table no_grupos needs a child table for claves, rather than a single tx_claves field containing multiple comma-delimited values.

Then you could perform the join on the child table by clave and use WHERE the tx_grupo_FK = 5
 
Last edited:
You must log in or register to reply here.

Similar threads

H
Using a query that creates a random result as the criteria for the a field in another table
Replies
3
Views
1,010
ebs17
E
L
Setting a DIM value as the result of a query
Replies
3
Views
571
Minty
Minty
I
Using a form to amend the search criteria of a query and display results in the form?
Replies
2
Views
934
imellor
I
B
DUAL Table in Msaccess for adding rows to a query result using Union
Replies
2
Views
9,077
baba
B
A
Access VBA using dates as criteria in a query
Replies
0
Views
1,208
armesca
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom