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

hi,

thats the first time I am a bit confused by a query not working.

I have this table:

gullevek=# \d test
                                 Table "public.test"
 Column  |       Type        |                       Modifiers
---------+-------------------+--------------------------------------------------------
 test_id | integer           | not null default
nextval('test_test_id_seq'::regclass)
 email_a | character varying |
 email_b | character varying |
Indexes:
    "test_pkey" PRIMARY KEY, btree (test_id)

with this content:

gullevek=# select * from test;
 test_id |    email_a    |   email_b
---------+---------------+-------------
       2 | test@test.com | bar@foo.com
       1 | foo@bar.com   |
(2 rows)

if I do this select:

select * from (select test_id, email_a FROM test WHERE email_a =
'moo@boo.com') as s, (select test_id, email_b from test where email_b =
'bar@foo.com') as t;

I get no rows back. should it return something? subquery a doesn't
return one, because the email does not exist, b returns something.

how do I have to make the query so I get a result back and in one row?

--
[ Clemens Schwaighofer                      -----=====:::::~ ]
[ TEQUILA\ Japan IT Group                                    ]
[                6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7703            Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.co.jp                                   ]

2007/10/12, Clemens Schwaighofer <cs@tequila.co.jp>:
> hi,
>
> thats the first time I am a bit confused by a query not working.
>
> I have this table:
>
> gullevek=# \d test
>                                  Table "public.test"
>  Column  |       Type        |                       Modifiers
> ---------+-------------------+--------------------------------------------------------
>  test_id | integer           | not null default
> nextval('test_test_id_seq'::regclass)
>  email_a | character varying |
>  email_b | character varying |
> Indexes:
>     "test_pkey" PRIMARY KEY, btree (test_id)
>
> with this content:
>
> gullevek=# select * from test;
>  test_id |    email_a    |   email_b
> ---------+---------------+-------------
>        2 | test@test.com | bar@foo.com
>        1 | foo@bar.com   |
> (2 rows)
>
> if I do this select:
>
> select * from (select test_id, email_a FROM test WHERE email_a =
> 'moo@boo.com') as s, (select test_id, email_b from test where email_b =
> 'bar@foo.com') as t;
>

you get 0 rows. moo@boo.com isn't anywhere and bar@foo.com cannot do
pair with any. else 0 * 1 = 0

Pavel

On 2007/10/12, at 23:22, Pavel Stehule wrote:

>>
>
> you get 0 rows. moo@boo.com isn't anywhere and bar@foo.com cannot do
> pair with any. else 0 * 1 = 0

Thanks, go it now. Basically very simple.

I probably need to approach the problem different. So both are read
"independent" or something like this.

--
[ Clemens Schwaighofer                      -----=====:::::~ ]
[ TEQUILA\ Japan IT Engineer                                 ]
[                6-17-2 Ginza Chuo-ku, Tokyo 104-0061, JAPAN ]
[ Tel: +81-(0)3-3545-7703            Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.co.jp        http://www.tbwajapan.co.jp ]

On 10/12/07, Clemens Schwaighofer <cs@tequila.co.jp> wrote:
> I probably need to approach the problem different. So both are read
> "independent" or something like this.

Also, email_a, email_b, etc. indicate that you need to consider
refactoring your schema.

You can find a lot of tutorials on normalization online.