Обсуждение: Query

Hi I have a table called "master" which look like this

id_master      |             month    |   day        |   infotype
<Serial> PK  |   <Varchar 3> |  <Varchar 3>  | <Varchar 5>
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1       |       May     |  27           |       121
2       |       May     |  27           |       122
3       |       May     |  27           |       123
4       |       May     |  28           |       121
5       |       May     |  28           |       122

Which Primary Key (id_master) is the Foreign Key (id_master) of another table called "masterdetail" which look like this

id_masterdetail        id_master        argument         color
<Serial> PK        |   <int4>         |  <Varchar 30>  | <Varchar 10>
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1               |       121     |  house        | blue
2               |       121     |  house                | blue
3               |       121     |  building     | white
4               |       122     |  building     | white
5               |       122     |  house                | yellow

I try tu run a query to ask for all the arguments, which color has the value "blue" and they had the date "May" "27"

I wrote something like this

select argument from masterdetail, master where color like 'blue' 
and master.month like 'May'
and master.day like '27'
and master.id_master = masterdetail.id_master;

But  I cant get the correct results!

How can I fix it?
---
Eduardo Vázquez Rodríguez <evazquez@insys-corp.com.mx>
Insys
Soluciones Integrales de Seguridad en TI
Tel (52 55) 5322 5218
Implantación
---

On Tue, Jul 13, 2004 at 13:07:36 -0500,
  Eduardo Vázquez Rodríguez <evazquez@insys-corp.com.mx> wrote:
> Hi I have a table called "master" which look like this
>
>
> id_master      |         month    |      day         |   infotype
> <Serial> PK  |   <Varchar 3> |  <Varchar 3>  | <Varchar 5>
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> 1    |    May    |  27        |    121
> 2    |    May    |  27        |    122
> 3    |    May    |  27        |    123
> 4    |    May    |  28        |    121
> 5    |    May    |  28        |    122
>
>
> Which Primary Key (id_master) is the Foreign Key (id_master) of another
> table called "masterdetail" which look like this

This is the problem infotype needs to be referenced, not id_master.

>
> id_masterdetail           id_master        argument         color
> <Serial> PK        |   <int4>         |  <Varchar 30>  | <Varchar 10>
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> 1        |    121    |  house     | blue
> 2        |    121    |  house        | blue
> 3        |    121    |  building    | white
> 4        |    122    |  building    | white
> 5        |    122    |  house        | yellow
>
>
> I try tu run a query to ask for all the arguments, which color has the value
> "blue" and they had the date "May" "27"
>
> I wrote something like this
>
> select argument from masterdetail, master where color like 'blue'
> and master.month like 'May'
> and master.day like '27'
> and master.id_master = masterdetail.id_master;

While eventually you want to change names and use '=' instead of 'like',
for the purposes of this question you want the last where condition to be:
and master.infotype = masterdetail.id_master::text;

>
>
> But  I cant get the correct results!
>
> How can I fix it?
> ---
> Eduardo Vázquez Rodríguez <evazquez@insys-corp.com.mx>
> Insys
> Soluciones Integrales de Seguridad en TI
> Tel (52 55) 5322 5218
> Implantación
> ---
>

Thanks !

It works !!



-----Original Message-----
From: Bruno Wolff III [mailto:bruno@wolff.to]
Sent: Tuesday, July 13, 2004 1:59 PM
To: Eduardo Vázquez Rodríguez
Cc: Postgres (pgsql-novice@postgresql.org)
Subject: Re: [NOVICE] Query

On Tue, Jul 13, 2004 at 13:07:36 -0500,
  Eduardo Vázquez Rodríguez <evazquez@insys-corp.com.mx> wrote:
> Hi I have a table called "master" which look like this
>
>
> id_master      |         month    |      day         |   infotype
> <Serial> PK  |   <Varchar 3> |  <Varchar 3>  | <Varchar 5>
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> 1    |    May    |  27        |    121
> 2    |    May    |  27        |    122
> 3    |    May    |  27        |    123
> 4    |    May    |  28        |    121
> 5    |    May    |  28        |    122
>
>
> Which Primary Key (id_master) is the Foreign Key (id_master) of
> another table called "masterdetail" which look like this

This is the problem infotype needs to be referenced, not id_master.

>
> id_masterdetail           id_master        argument         color
> <Serial> PK        |   <int4>         |  <Varchar 30>  | <Varchar 10>
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> 1        |    121    |  house     | blue
> 2        |    121    |  house        | blue
> 3        |    121    |  building    | white
> 4        |    122    |  building    | white
> 5        |    122    |  house        | yellow
>
>
> I try tu run a query to ask for all the arguments, which color has the
> value "blue" and they had the date "May" "27"
>
> I wrote something like this
>
> select argument from masterdetail, master where color like 'blue'
> and master.month like 'May'
> and master.day like '27'
> and master.id_master = masterdetail.id_master;

While eventually you want to change names and use '=' instead of 'like', for
the purposes of this question you want the last where condition to be:
and master.infotype = masterdetail.id_master::text;

>
>
> But  I cant get the correct results!
>
> How can I fix it?
> ---
> Eduardo Vázquez Rodríguez <evazquez@insys-corp.com.mx> Insys
> Soluciones Integrales de Seguridad en TI Tel (52 55) 5322 5218
> Implantación
> ---
>