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

[Pat Hartman]

Please show me the two tables in the relationship window AND post the data in both tables. I have no idea what you are working from.

 

[cheekybuddha]

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:

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:

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:

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

yields:

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'

 

[pacctono]

Friends,

This is the solution for my question:

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]

Do you know what a Cartesian Product is? If this is Access, you REALLY need to include the actual join type. Do not create a Cartesian Product and filter it later. The end result may be the same but Access thinks you mean it. Other RDBMS may know you did something stupid and ignore the Cartesian Product and substitute the join but Access doesn't. Sometimes, you need to be the adult in the room and guide the solution and this is one of those times. Once you get beyond the test data stage, you will understand why. You will probably survive anyway in this case because the Group table probably isn't very large and you will only be multiplying the c table by the number of rows in the group table. How bad this process is depends on whether access creates the Cartesian Product first and then applies the criteria or applies the where clause first to each table and then creates the Cartesian Product.

SELECT c.tx_clave, c.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';

And, unless you really don't want ALL of the group 5 values, then you also don't need the instr(). If you do need the instr(), then you DON"T need the join and the group = 5. The entire point of the join was to eliminate the instr()

 

[cheekybuddha]

The entire point of the join was to eliminate the instr()

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.

If you do need the instr(), then you DON"T need the join and the group = 5

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?

 

[pacctono]

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
;

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?

Yes, Thanks!. I just have to add parentesis

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 have no idea what the actual requirement is. There are two ways to do this.
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.
2. If you want only SOME of the claves, then you need to select using the Instr() for only the specific groups and you would NOT use the join.

You would NEVER use both.

I don't see how the OP can eliminate the InStr() without properly normalising the tables

The tables are almost normalized. You join to the group table on clave which would seem to bring back two rows, one from group 05 and one from group 02 But, then the criteria reduces the duplication to just the values from 05.

 

[cheekybuddha]

You join to the group table on clave which would seem to bring back two rows

How do you propose to do this without using InStr() (or some other, perhaps LIKE ?)

"A01" != "'A01','A04','A07','A08','A10','A13','A16','A19','A22','A25','A84'"

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.

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.

The tables are almost normalized.

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

 

[Pat Hartman]

Can you give an example of the query you propose here in SQL?

I posted a sample in #24. There is no point in beating this dead horse without the actual schema. If the OP wants to post it with some actual data, we can determine if the join will or will not work to eliminate the instr.

 

[cheekybuddha]

If the OP wants to post it with some actual data

See Post #22

 

[Pat Hartman]

See Post #22

The schema is hopeless. The OP is lucky to get anything.

 

[cheekybuddha]

The schema is hopeless.

On that, I think all participants in the thread agree!