I can't figure this out myself. Hope someone could help me.

I have three tables: `users`, `cards`, `referees` and `users_cards`

**Users:**

```
id  |   userName  |   referee_id
----------------------------
1   |   u1        |    1
2   |   u2        |    1
3   |   u3        |    2

```

**Referees:**

```
id  |   refName
--------------
1   |   ref1
2   |   ref2

```

**Cards:**

```
id  |   cardName
--------------
1   |   card1
2   |   card2

```

**Users\_Cards:**

```
user_id  |   card_id  |   color   |   number
-------------------------------------------
1        |   1        |   red     |   123
1        |   2        |   yellow  |   312
2        |   2        |   yellow  |   523
3        |   1        |   red     |   344

```

The result I want to get is:

```
id   |   userName   |   refName   |   cards
1    |      u1      |    ref1     |   card1: red (123), card2: yellow (312)
2    |      u2      |    ref1     |   card2: yellow (523)
3    |      u3      |    ref2     |   card1: red (344)

```

And so on...

All I can get is multiple rows when user has more than a one card. So how can I combine them like this.

Thank you to anyone, who could help me!´

EDIT:

Currently I'm just using `LEFT JOIN`s

```
SELECT UserName, refName cardName, color, number,
FROM users
LEFT JOIN referees ON users.referee_id = referees.id
LEFT JOIN users_cards ON users.id = users_cards.user_id
LEFT JOIN cards ON dbo.users_cards.card_id = cards.id

```