Join 2 rows in SQL?

[Sobakaa]

Hello,

I have 3 tables, user, role and userrole. Userrole stores user_id and role_id, so every user is associated with some role(s).
So, when i use this query to view all users, and if user has several roles, i can see several rows with identical info and just role column is different.

select  userrole.USERNAME
    ,   FIRSTNAME
    ,   LASTNAME
    ,   LOCKED
    ,   EMAILADDRESS
    ,   ROLENAME   
from    user, role, userrole
where userrole.USERNAME = user.USERNAME and userrole.ROLEID = role.ROLEID

result:

coguserba   cogba   cogba   0   [e-mail]   2
coguserba   cogba   cogba   0   [e-mail]   5

I tried to use JOIN, but found that it won't help me in this case. Unfortunately i'm not an expert in SQL, so can you please tell me if there is some way to combine such fileds to look like this without extensive shell scripting:

coguserba cogba cogba 0 [e-mail] 2;5

The final goal would be to export this info into csv file (already done with sqlplus), with no nearly identical strings.

[Taywin]

You need a JOIN for sure. The only different is how to use your JOIN. This is from the top of my head... You may try...

SELECT user.USERNAME, user.FIRSTNAME, user.LASTNAME, user.LOCKED, user.EMAILADDRESS, role.ROLENAME
FROM user
  INNER JOIN userrole
    ON user.id=userrole.user_id
  INNER JOIN role
    ON role.id=userrole.role_id

[WayneZ]

I don't believe you can concatenate the values from the same column in several records into a single value in a single SQL statement, at least not one that someone who is not an expert in SQL could create. If you want to investigate further I suggest searching the internet ("googling") with a phrase such as "SQL concatenate rows".

[Taywin]

Huh? What are you talking about? The OP wants to join table data from multiple tables that contain comparable keys (foreign key or anything that can be used to compare). The data should be join together into 1 data row. It is possible and can be done with 1 SQL call. Also, google results depends on the "keyword" you enter. If you can't get a good set of results with your current keywords, it does not mean it is impossible becase your keywords may be unclear, too broad, or too narrow.

[WayneZ]

The request is to create one row on which the values 2 and 5 are concatenated into a single column. Your statement creates 2 rows. I did not assume it was impossible because of poor search keywords. It's something I've looked into several times in several venues and a simple JOIN doesn't cut it.