Re: multiple column to onec column

zab08 <zab08@126.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°