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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to