Re: multiple column to onec column

zab08 <zab08(at)126(dot)com> wrote:

>
> I have two table:
>
>
> CREATE TABLE roles(role_name varchar(255) primary key);
>
> CREATE TABLE roles_permissions(permission varchar(100), role_name varchar
> (100));
>
> here is result by :
> SELECT * from roles;
> role_name
> -----------
> role1
> role2
> (2 rows)
>
> here is result by :
> SELECT * from roles_permissions ;
> role_name | permission
> -----------+-------------
> role1 | permission1
> role1 | permission2
> role2 | permission1
> (3 rows)
>
> ---------------------------------------- -----------------------------------
>
> after the command:
> SELECT * from roles, roles_permissions;

That's a CROSS-JOIN

> role_name | role_name | permission
> -----------+-----------+-------------
> role1 | role1 | permission1
> role1 | role1 | permission2
> role1 | role2 | permission1
> role2 | role1 | permission1
> role2 | role1 | permission2
> role2 | role2 | permission1
>
>
> the expected result is:
> role_name | permission
> -----------+-------------
> role1 | permission1,permission2,
> & nbsp;role2 | permission1
>
>
> for short:
> afer join of two table, here is some repeat columns,

yeah, it's a cross-join. Rewrite your query, adding a WHERE-condition:

where roles.role_name = roles_permissions.role_name

Regards, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°