query for multiple values

eponcede · #1 (**permalink**) 01-19-12, 11:17

I am trying to elaborate a query where I want to search for, these values (FB,PH,PN,PU,RN,SM,YQ,0A,1P,).
When I search for 1 values i have no problem, however when I add a second one I get no results.. can anybody guide me on how to fix this... This is my query:

Code:

SELECT
	"TELMEX_CON_ENE"."RAZON_SOCIAL",
  "TELMEX_CON_ENE"."CUENTA",
	"TELMEX_CON_ENE"."SUBCUENTA",
	"TELMEX_CON_ENE"."CLAVE_CONCEPTO",
	"TELMEX_CON_ENE"."MES",
	"SERVICIO"."NOMBRE",
	"PROVEEDOR"."NOMBRE"
FROM 
  public."TELMEX_CON_ENE", 
	public."PROVEEDOR",
	public."SERVICIO"
WHERE 
	"SERVICIO"."NOMBRE" ='TELEFONIA FIJA' AND
	"PROVEEDOR"."NOMBRE" = 'TELMEX' AND
	"TELMEX_CON_ENE"."CLAVE_CONCEPTO" LIKE '%FB-%' AND
	"TELMEX_CON_ENE"."CLAVE_CONCEPTO" LIKE '%PH-%';

futurity · #2 (**permalink**) 01-20-12, 11:39

Some comments about your query in general:

* You are not using any JOIN ON conditions to join your tables, which means you are doing a CROSS JOIN for all three of them, which can quickly become very inefficient.

* Your LIKE conditions are also very inefficient: starting the search term with a wildcard (%) means that the database cannot use any indexes on that column and is forced to do a sequential scan of the entire table. If you can, remove the starting wildcard.

As for fixing your query, try:

Code:

WHERE
    "SERVICIO"."NOMBRE" ='TELEFONIA FIJA' 
    AND "PROVEEDOR"."NOMBRE" = 'TELMEX' 
    AND ("TELMEX_CON_ENE"."CLAVE_CONCEPTO" LIKE '%FB-%' 
        OR "TELMEX_CON_ENE"."CLAVE_CONCEPTO" LIKE '%PH-%'
        OR ...)

Internet Services

Web Development

Digital Marketing

Consumer Tech