Thread: query with =ALL
Hi all,
i have an strange result here, i'm using 7.4.2 on redhat 8
i have a query like this
SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
CPA.cpa_fechavencimiento
FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
'2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = ALL
(SELECTcur_paralelo FROM aca_t_curso WHERE
ent_codigo = 1 AND
sec_codigo = 1 AND
ani_codigo = '2004-2005' AND
cic_codigo = 1 AND
esp_codigo = 0 AND
cur_codigo = 1)
that bring no values but if i do this (the same query without the =ALL part)
SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
CPA.cpa_fechavencimiento
FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
'2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1
get a resultset, one would think that the =ALL subquery is filtering out
that rows but the cur_paralelo has same values in both sides
select distinct cur_paralelo from rec_m_cuadropagos WHERE ent_codigo = 1 AND
sec_codigo = 1 AND
ani_codigo = '2004-2005' AND
cic_codigo = 1 AND
esp_codigo = 0 AND
cur_codigo = 1
intersect
SELECT cur_paralelo FROM aca_t_curso WHERE ent_codigo = 1 AND
sec_codigo = 1 AND
ani_codigo = '2004-2005' AND
cic_codigo = 1 AND
esp_codigo = 0 AND
cur_codigo = 1
this query proves that its result is 'A', 'B', 'C'
any idea, is something wrong in my thinking?
thanx in advance,
Jaime Casanova
_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
On Mon, 14 Jun 2004, Jaime Casanova wrote: > i have an strange result here, i'm using 7.4.2 on redhat 8 > > i have a query like this > > SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, > CPA.cpa_fechavencimiento > FROM rec_m_cuadropagos CPA, rec_m_rubro RUB > WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo > AND > CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = > '2004-2005' AND > CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND > CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso WHERE > ent_codigo = 1 AND > > sec_codigo = 1 AND > > ani_codigo = '2004-2005' AND > > cic_codigo = 1 AND > > esp_codigo = 0 AND > > cur_codigo = 1) > > that bring no values but if i do this (the same query without the =ALL part) > > SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, > CPA.cpa_fechavencimiento > FROM rec_m_cuadropagos CPA, rec_m_rubro RUB > WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo > AND > CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = > '2004-2005' AND > CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 > > get a resultset, one would think that the =ALL subquery is filtering out > that rows but the cur_paralelo has same values in both sides > > select distinct cur_paralelo from rec_m_cuadropagos WHERE ent_codigo = 1 AND > > sec_codigo = 1 AND > > ani_codigo = '2004-2005' AND > > cic_codigo = 1 AND > > esp_codigo = 0 AND > > cur_codigo = 1 > intersect > SELECT cur_paralelo FROM aca_t_curso WHERE ent_codigo = 1 AND > > sec_codigo = 1 AND > > ani_codigo = '2004-2005' AND > > cic_codigo = 1 AND > > esp_codigo = 0 AND > > cur_codigo = 1 > > this query proves that its result is 'A', 'B', 'C' > > any idea, is something wrong in my thinking? AFAIK, The query isn't check all values that meet the other criteria against all values of the subquery but instead check the value from each row that meets the other criteria against all the values in the subquery. If the values in the subquery are distinct, it's not going to return true.
Jaime Casanova wrote: > Hi all, > > i have an strange result here, i'm using 7.4.2 on redhat 8 > > i have a query like this [snip] > this query proves that its result is 'A', 'B', 'C' > any idea, is something wrong in my thinking? Do you have any null values involved? That might well interfere (though I admit I haven't looked in detail) -- Richard Huxton Archonet Ltd
>Jaime Casanova wrote:
>>Hi all,
>>
>>i have an strange result here, i'm using 7.4.2 on redhat 8
>>
>>i have a query like this
>
>[snip]
>
>>this query proves that its result is 'A', 'B', 'C'
>>any idea, is something wrong in my thinking?
>Do you have any null values involved? That might well interfere (though I
>admit I haven't looked in >detail)
There are no null values here, i'm sure of that because all the fields in
the subquery included the one i'm returning are part of the PK
>--
> Richard Huxton
> Archonet Ltd
and when i do the selects individually i got the correct results
SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
CPA.cpa_fechavencimiento
FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
'2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'A '
1 Matr�cula 100 05/10/2005
SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
CPA.cpa_fechavencimiento
FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
'2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'B '
1 Matr�cula 100 05/10/2005
SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
CPA.cpa_fechavencimiento
FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
'2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'C '
1 Matr�cula 100 05/10/2005
_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
On Mon, 14 Jun 2004, Jaime Casanova wrote: > i have an strange result here, i'm using 7.4.2 on redhat 8>> i have a query like this>> SELECT CPA.rub_codigo, RUB.rub_descripcion,CPA.cpa_valor,> CPA.cpa_fechavencimiento> FROM rec_m_cuadropagos CPA, rec_m_rubro RUB> WHERE RUB.ent_codigo= CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo> AND> CPA.ent_codigo = 1 AND CPA.sec_codigo = 1AND CPA.ani_codigo => '2004-2005' AND> CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND> CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso WHERE> ent_codigo = 1 AND>> sec_codigo = 1 AND>>ani_codigo = '2004-2005' AND>> cic_codigo = 1 AND>> esp_codigo = 0 AND>> cur_codigo = 1)>> that bring no values butif i do this (the same query without the =ALL part)>> SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,> CPA.cpa_fechavencimiento> FROM rec_m_cuadropagos CPA,rec_m_rubro RUB> WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo> AND> CPA.ent_codigo= 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo => '2004-2005' AND> CPA.cic_codigo = 1 AND CPA.esp_codigo= 0 AND CPA.cur_codigo = 1>> get a resultset, one would think that the =ALL subquery is filtering out> thatrows but the cur_paralelo has same values in both sides>> select distinct cur_paralelo from rec_m_cuadropagos WHERE ent_codigo= 1 AND>> sec_codigo = 1 AND>> ani_codigo = '2004-2005' AND>> cic_codigo = 1 AND>> esp_codigo = 0 AND>> cur_codigo = 1> intersect>SELECT cur_paralelo FROM aca_t_curso WHERE ent_codigo = 1 AND>> sec_codigo = 1 AND>> ani_codigo = '2004-2005' AND>>cic_codigo = 1 AND>> esp_codigo = 0 AND>> cur_codigo = 1>> this query proves that its result is 'A', 'B', 'C'>> anyidea, is something wrong in my thinking? AFAIK, The query isn't check all values that meet the other criteria against all values of the subquery but instead check the value from each row that meets the other criteria against all the values in the subquery. If the values in the subquery are distinct, it's not going to return true. Is there another solution instead the one i'm using can you help me in that? _________________________________________________________________ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail
On Mon, 14 Jun 2004, Jaime Casanova wrote: > On Mon, 14 Jun 2004, Jaime Casanova wrote: > > > i have an strange result here, i'm using 7.4.2 on redhat 8 > > > > i have a query like this > > > > SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, > > CPA.cpa_fechavencimiento > > FROM rec_m_cuadropagos CPA, rec_m_rubro RUB > > WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo > > AND > > CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = > > '2004-2005' AND > > CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 > AND > > CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso WHERE > > ent_codigo = 1 AND > > > > sec_codigo = 1 AND > > > > ani_codigo = '2004-2005' AND > > > > cic_codigo = 1 AND > > > > esp_codigo = 0 AND > > > > cur_codigo = 1) > > > > that bring no values but if i do this (the same query without the =ALL > part) > AFAIK, The query isn't check all values that meet the other criteria > against all values of the subquery but instead check the value from each > row that meets the other criteria against all the values in the subquery. > If the values in the subquery are distinct, it's not going to return true. > Is there another solution instead the one i'm using can you help me in that? What precisely are you looking for as your output? Only those combinations of the four output attributes that meet the other criteria and that have exactly the same set as in the subselect? Hmm, I'm not sure how to do that off hand, will think about it, but hopefully someone will have an answer.
> On Mon, 14 Jun 2004, Jaime Casanova wrote:>> > On Mon, 14 Jun 2004, Jaime Casanova wrote:> >> > > i have an strangeresult here, i'm using 7.4.2 on redhat 8> > >> > > i have a query like this> > >> > > SELECT CPA.rub_codigo,RUB.rub_descripcion, CPA.cpa_valor,> > > CPA.cpa_fechavencimiento> > > FROM rec_m_cuadropagos CPA, rec_m_rubroRUB> > > WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo> > > AND> > > CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo => > > '2004-2005' AND> > > CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1> > AND> > > CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso WHERE> > > ent_codigo = 1 AND> > >> > > sec_codigo = 1 AND> > >> > > ani_codigo = '2004-2005' AND> > >> > >cic_codigo = 1 AND> > >> > > esp_codigo = 0 AND> > >> > > cur_codigo = 1)> > >> > > that bring no values butif i do this (the same query without the =ALL> > part)> > AFAIK, The query isn't check all values that meet the other criteria> > against all values of the subquerybut instead check the value from each> > row that meets the other criteria against all the values in the subquery.> > If the values in the subquery are distinct, it's not going to return true. > > Is there another solution instead the one i'm using can you help me in that? > What precisely are you looking for as your output? Only those> combinations of the four output attributes that meet theother criteria> and that have exactly the same set as in the subselect? Hmm, I'm not sure> how to do that off hand, will think about it, but hopefully someone will> have an answer. hi all, What i'm trying to do is to obtain the same result that i should obtain if i do: SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA, rec_m_rubro RUB WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'A ' intersect SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA, rec_m_rubro RUB WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'B ' intersect SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA, rec_m_rubro RUB WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'C ' but i cannot use the intersect solution becuase i don't know how many values of cur_paralelo could be involved becuase that depends on user input. maybe someone can help. thanx in advance, Jaime Casanova _________________________________________________________________ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail
"Jaime Casanova" <el_vigia_ec@hotmail.com> writes:
> AND
> CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso ...)
Wait a second ... we are all overthinking the problem. The subselect
returns three *different* values. It is not possible for any
CPA.cur_paralelo values to be simultaneously equal to all three.
So this test certainly fails at every row of CPA.
Perhaps you meant "= ANY"?
regards, tom lane
Hi all, > > AND > > CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso ...) > >Wait a second ... we are all overthinking the problem. The subselect >returns three *different* values. It is not possible for any >CPA.cur_paralelo values to be simultaneously equal to all three. >So this test certainly fails at every row of CPA. > >Perhaps you meant "= ANY"? Not really becuase ANY has the same efect that IN and what i want is all the results that are equal in all the rows in any of the cur_paralelo values. But must be in all the cur_paralelo or nothing. thanx in advance, Jaime Casanova _________________________________________________________________ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus
"Jaime Casanova" <el_vigia_ec@hotmail.com> writes:
>> Perhaps you meant "= ANY"?
> Not really becuase ANY has the same efect that IN and what i want is all the
> results that are equal in all the rows in any of the cur_paralelo values.
> But must be in all the cur_paralelo or nothing.
You're not expressing yourself clearly, because as far as I can
understand you there are guaranteed to be no such results.
regards, tom lane
>You're not expressing yourself clearly, because as far as I can >understand you there are guaranteed to be no such results. > > regards, tom lane ok, the output i want is equivalent to: SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA, rec_m_rubro RUB WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'A ' intersect SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA, rec_m_rubro RUB WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'B ' intersect SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA, rec_m_rubro RUB WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'C ' but i cannot use that solution because the columns in wich i want to so such type of query are various and depends on user input _________________________________________________________________ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail
On Mon, 14 Jun 2004, Tom Lane wrote: > "Jaime Casanova" <el_vigia_ec@hotmail.com> writes: > > AND > > CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso ...) > > Wait a second ... we are all overthinking the problem. The subselect > returns three *different* values. It is not possible for any > CPA.cur_paralelo values to be simultaneously equal to all three. > So this test certainly fails at every row of CPA. > > Perhaps you meant "= ANY"? I think what he wants is to output distinct sets of output columns where for each of these sets of output columns there exists a set of rows that meet the other conditions and that the set of cur_paralelo values for that set of rows is the same as (or perhaps is a superset of) the set of values returned from the subselect.