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

[pacctono]

Hello, friends!

I have a simple query, that works as expected:

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

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?

 

[tvanstiphout]

> 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.

 

[pacctono]

> 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.

Well, the first query returns 11 rows, as expected. The second one returns none.

 

[plog]

Well, the first query returns 11 rows, as expected. The second one returns none.

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.

 

[pacctono]

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.

If I execute this 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:

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:

SELECT g.tx_grupo, g.tx_descripcion, g.tx_claves
FROM no_grupos AS g;

I get this:

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:

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:

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:

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.

 

[plog]

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.

 

[cheekybuddha]

g.tx_claves=:'A01','A04','A07','A08','A10','A13','A16','A19','A22','A25','A84'; WHERE g.tx_grupo = '05'

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:

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;

 

[Pat Hartman]

Do NOT use a subquery. Use a join to the group table. That lets you select on group rather than claves.

I'm assuming that you are working with an ACE BE. Access does not optimize subqueries well and so you should only use subqueries when you actually need one. This is not that situation. You need a join to solve your problem. For reasons that escape me, some people just want to use subqueries all the time. I guess if all you have is a hammer, every problem looks like a nail. Use the correct (and most efficient) tool to solve the problem

 

[pacctono]

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:

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;

Horrible, too, but works:

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;

 

[cheekybuddha]

What happens when you have tx_clave = 'A100' ?

 

[pacctono]

What happens when you have tx_clave = 'A100' ?

tx_clave is limited to 3 characters.

 

[CJ_London]

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]

WHERE instr(dlookup("tx_claves","no_grupos","tx_grupo = '05'"),c.tx_clave) >0;

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.

 

[pacctono]

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.

Thanks:

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:

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;

 

[Pat Hartman]

You have to use a specific join. Use Inner if the FK is required, use Left if the FK is optional. Otherwise you will be creating a Cartesian Product which will take much longer to execute and may not be updateable.

SELECT c.tx_clave, g.tx_descripcion
FROM no_conceptos AS c Inner Join no_grupos as g ON c.tx_grupo = g. tx_grupo
Where c.tx_grupo = '05'

You didn't post your schema so I don't actually know what the query should look like but it will be something like the above. Use the QBE to make the query.
Select the first table and then the second from the list.
Draw the join line between the group field in the two tables.
select the columns you want from both tables
Add the where clause to the "c" table, NOT the group table.

 

[cheekybuddha]

Using a join would look more like:

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]

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.

 

[cheekybuddha]

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.

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?

 

[Pat Hartman]

The second table is a group of Claves

I think my join is wrong. It is hard without seeing the tables. I think Clave is in the first table but group is not so you join to group on clave and the criteria is on just the group rather than using IN() with all the claves in group 5.

SELECT c.tx_clave, g.tx_descripcion
FROM no_conceptos AS c Inner Join no_grupos as g ON c.tx_clave = g. tx_clave
Where g.tx_grupo = '05'

 

[cheekybuddha]

Ad f I execute this code:

SELECT g.tx_grupo, g.tx_descripcion, g.tx_claves
FROM no_grupos AS g;

I get this:

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'

Above from the OP in Post #1