RE: [GENERAL] question 1 - Mailing list pgsql-general

I don't know why you are experiencing a problem here but I think I can
speed up that query.  Let's try a rewrite.
 SELECT * FROM libro l WHERE EXISTS(
   SELECT la.id_libro FROM libro_autore la, autore a
    WHERE la.id_libro = l.id_libro_key AND
          la.id_autore = a.id_autore AND
          ((a.cognome = 'King' AND a.nome = 'Stephen') OR
           (a.cognome = 'Clancy' AND a.nome = 'Tome'))

See if that gives you what you are expecting.

> -----Original Message-----
> Hi, i have this query:
> select * from libro where id_libro_key in (select id_libro from
> libro_autore where id_auto
> re in (select id_autore from autore where ((cognome = 'King' and nome
> =
> 'Stephen') OR (cog
> nome = 'Clancy' and nome = 'Tom'))))\g
> that return me this error:
> ERROR:  There is no operator '=' for types 'int4' and 'varchar'
>         You will either have to retype this query using an explicit
> cast,
>         or you will have to define the operator using CREATE OPERATOR
>
> but if I split the query in 2 part I obtain:
> 1)select id_libro from libro_autore where id_autore in (select
> id_autore
> from autore where
>  ((cognome = 'King' and nome = 'Stephen') OR (cognome = 'Clancy' and
> nome
> = 'Tom')))\g
> id_libro
> --------
>       11
>       12
> (2 rows)
>
> 2)esame=> select * from libro where id_libro_key in ( 11,12)\g
> titolo      |id_libro|id_utente|collocazione|casa_edit|
> data_publ|tipo|difetto|id_libro_ke
> y
> ------------+--------+---------+------------+---------+----------+----
> +-------+-----------
> -
> It          |d1      |         |d1          |apo      |05-12-1940|
> 1|
> |          1
> 1
> Il talismano|s1      |         |d1          |aop2     |05-12-1985|
> 1|
> |          1
> 2
> (2 rows)
>
> why I can not make the union/join ?
> where I'm wrong ?
> these are the table:
> esame=> \d libro
>
> Table    = libro
> +----------------------------------+----------------------------------
> +-------+
> |              Field               |              Type
> |
> Length|
> +----------------------------------+----------------------------------
> +-------+
> | titolo                           | varchar() not null
> |
> 80 |
> | id_libro                         | varchar() not null
> |
> 10 |
> | id_utente                        | int4
> |
> 4 |
> | collocazione                     | varchar() not null
> |
> 10 |
> | casa_edit                        | varchar()
> |
> 20 |
> | data_publ                        | date
> |
> 4 |
> | tipo                             | int4
> |
> 4 |
> | difetto                          | varchar()
> |
> 40 |
> | id_libro_key                     | int4 not null default nextval (
> |
> 4 |
> +----------------------------------+----------------------------------
> +-------+
>
>
> esame=> \d libro_autore
>
> Table    = libro_autore
> +----------------------------------+----------------------------------
> +-------+
> |              Field               |              Type
> |
> Length|
> +----------------------------------+----------------------------------
> +-------+
> | id_libro                         | varchar() not null
> |
> 10 |
> | id_autore                        | int4 not null
> |
> 4 |
> +----------------------------------+----------------------------------
> +-------+
>
>
>
>
> "Il divertimento e' giusto se la scimmia ci prende gusto"
> --
> Italian Linux Press: http://ziobudda.enter.it/ILP/
> --
> Morelli 'ZioBudda' Davide Michel - Member of Pluto Linux User Group
> michel@michel.enter.it - http://ziobudda.enter.it/
> Linux Problem? Ask to linux@media.dsi.unimi.it
> "/dev/ziobudda: access to /var/tmp/beer denied, use
> /var/adm/pineapple"
>